CODEWITHSUNDEEP
sql
Howiecamp
Howiecamp

Reputation: 3111

How do I find the most recently dated record for each ID in a set of IDs where any ID can have multiple records each for a particular date?

I've got a data set that looks as follows:

The first column is an auto-increment primary key. The second column is an ID number for whatever, maybe 3 rocks with IDs 1, 2 and 3 respectively. (I probably should have used the standard customer and order example but oh well.) The third column is a date when I threw the rock. I track the date each time the rock is thrown, hence the multiple IDs (the second column) each with a throwing time.

I want a query to return the rock ID and most recent date for each ID. The result of course would have a single record for each ID - the one with the latest access time.

I'm struggling with the possible combination of "DISTINCT", "TOP 1" and "GROUP BY" clauses that gives the result I want.

Upvotes: 0

Views: 154

Answers (1)

niktrs
niktrs

Reputation: 10066

SELECT id, max(date)
FROM table
GROUP BY id

If you want also the autoincrement row id then

SELECT t1.rowid, t1.id, t1date
FROM table t1
JOIN (SELECT id,max(date) date FROM table1 t2 GROUP BY id) t2 ON t1.id = t2.id AND t1.date = t2.date

Upvotes: 1

Related Questions