Question 21640 in Mysql Question posted in
The official MySQL documentation can be found here.

Get Top 5 latest records for different IDs in SQL – Mysql

I have a table named Work_Items like this:

enter image description here

Assume there are lots of Names (i.e., E,F,G,H,I etc.,) and their respective Date and Produced Items in this table. It’s a massive table, so I’d want to write an optimised query.

In this, I want to query the latest A,B,C,D records.

I was using the following query:

SELECT * FROM Work_Items WHERE Name IN ('A','B','C','D') ORDER BY Date DESC OFFSET 0 LIMIT 4

But the problem with this query is, since I’m ordering by Date, the latest 4 records I’m getting are:

enter image description here

I want to get this result:

enter image description here

Please help me in modifying the query. Thanks.

Tags:

2

Answers


  1. 0

    On MySQL 8+, we can use ROW_NUMBER:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC) rn
    FROM Work_Items
    WHERE Name IN ('A', 'B', 'C', 'D')
)

SELECT Name, Date, ProducedItems
FROM cte
WHERE rn = 1
ORDER BY Name;
    Login or Signup to reply.
  2. 0

    You can use inner join as follows, its working on any mysql version:

    select w.name, w.`date`, w.ProducedItems
from _Work_Items w
inner join (
    select name, max(date) as `date`
    from _Work_Items
    group by name
) as s on s.name = w.name and s.`date` = w.`date` ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search