Get Top 5 latest records for different IDs in SQL

Question

I have a table named Work_Items like this:

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:

I want to get this result:

Please help me in modifying the query. Thanks.

Don't ask a question tagged MySQL and asking about SQL and then edit it to be about Azure CosmosDB after answers have already been posted. If you have a CosmosDB question ask a new question about that — Martin Smith, Dec 22 '22 at 09:18

score 1 · Answer 1 · answered Dec 22 '22 at 08:29

1

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;

answered Dec 22 '22 at 08:29

Tim Biegeleisen

502,043
27
286
360

Thanks a bunch. Any idea how it would translate in Azure CosmosDB? – HerraJohn Dec 22 '22 at 08:48
No idea. You tagged for MySQL and you got a MySQL answer. – Tim Biegeleisen Dec 22 '22 at 08:51
I'm sorry. I actually wanted it for CosmosDB. I edited it. I appreciate your help. Thank you. – HerraJohn Dec 22 '22 at 09:01

score 1 · Answer 2 · answered Dec 22 '22 at 08:54

1

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` ;

answered Dec 22 '22 at 08:54

SelVazi

10,028
2
13
29

I edited the question to get answer for CosmosDB instead of MySQL. If you can translate this to that, I'll accept your answer. Either way, Thank you for your help. – HerraJohn Dec 22 '22 at 09:02
never used CosmosDB, did a little search found that `inner join` must be changed to `join` maybe it will work ! https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/join – SelVazi Dec 22 '22 at 09:40

Get Top 5 latest records for different IDs in SQL

2 Answers2