Select distinct rows whilst grouping by max value
14,459
Solution 1
In databases that support analytic functions, you could use row_number()
:
select *
from (
select row_number() over (partition by ID
order by EventTime desc) as rn
, *
from YourTable
) as SubQueryAlias
where rn = 1
Solution 2
SELECT
ID, Name, EventTime, State
FROM
MyTable mt
WHERE EventTime = (SELECT MAX(EventTime) FROM MyTable sq WHERE mt.ID = sq.ID)
Solution 3
You can try this:-
SELECT ID, Name, EventTime, State
FROM mytable mm Where EventTime IN (Select MAX(EventTime) from mytable mt where mt.id=mm.id)
Solution 4
You did not specify what database you are using but you should be able to use an aggregate function in a subquery to get the max event time for each id:
select t1.id,
t1.name,
t1.eventtime,
t1.state
from mytable t1
inner join
(
select max(eventtime) eventtime, id
from mytable
group by id
) t2
on t1.id = t2.id
and t1.eventtime = t2.eventtime
order by t1.id;
Author by
lethalMango
Updated on June 26, 2022Comments
-
lethalMango over 1 year
I currently have the following table:
ID | Name | EventTime | State 1001 | User 1 | 2013/07/22 00:00:05 | 15 1002 | User 2 | 2013/07/23 00:10:00 | 100 1003 | User 3 | 2013/07/23 06:15:31 | 35 1001 | User 1 | 2013/07/23 07:13:00 | 21 1001 | User 1 | 2013/07/23 08:15:00 | 25 1003 | User 3 | 2013/07/23 10:00:00 | 22 1002 | User 2 | 2013/07/23 09:18:21 | 50
What I need is the
state
for each distinctuserid
from the lasteventtime
similar to below:ID | Name | EventTime | State 1001 | User 1 | 2013/07/23 08:15:00 | 25 1003 | User 3 | 2013/07/23 10:00:00 | 22 1002 | User 2 | 2013/07/23 09:18:21 | 50
I need something similar to the following but I can't quite get what I need.
SELECT ID, Name, max(EventTime), State FROM MyTable GROUP BY ID
-
Gordon Linoff over 10 yearsAlthough I much prefer
row_number()
, this might perform the best under some circumstances. -
fancyPants over 10 yearsAlso this is standard SQL :) Row_number() is not available everywhere.
-
Gordon Linoff over 10 years. .
row_number()
is ANSI standard SQL. The problem is, no real database really implements the standard. -
Hart CO over 10 yearsThis doesn't relate the MAX(EventTime) to the ID, it's just limiting to the eventtimes that are the MAX() for any ID, it would fail if a non-max eventtime for one ID happened to be the max for another ID.
-
fancyPants over 10 yearsOh really? Okay, didn't know that. Thanks for the info.
-
Vivek Sadh over 10 years@Goat CO You are absolutely right bro. I got what you said. I have updated it and its working fine now. :)
-
Hart CO over 10 yearsYeah that fixes it, it's a correlated subquery now, so it's evaluating the max for each ID. It could just as easily be
=
instead ofIN
because the subquery returns one value per ID.