CODEWITHSUNDEEP
mysql
ventsyv
ventsyv

Reputation: 3532

Select latest row for each unique combination

Let's say I have the following table:

Id          SubId        Date
----        -----        ----
1           1 
1           1
1           2
1           3
2           1
2           2 
2           2
2           3

How can I select the latest timestamp for each unique id/subID pair? The results should be:

Id          SubId        Date
----        -----        ----
1           1            latest 1-1
1           2            latest 1-2
1           3            latest 1-3 
2           1
2           2 
2           3

SQL Server has Over Partition syntax that could be used to do something like tha t but as far as I'm aware MySQL does not support that?

I want to avoid having to execute multiple queries and prefer not to have to rely on some exotic MySQL specific syntax if possible.

Upvotes: 0

Views: 74

Answers (2)

m0bi5
m0bi5

Reputation: 9472

Try this and let me know if it works:

SELECT id, DISTINCT(subid) from table ORDER BY date DESC;

Upvotes: 0

Gonzalo.-
Gonzalo.-

Reputation: 12682

What about using group by ?

Select 
Id, SubId, Max(Date)
from myTable
group by Id, SubId

Upvotes: 1

Related Questions