Selecting individual rows when grouping by maximum value

Question

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 

I need state for each individual userid from the last eventtime , as shown 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 cannot get what I need.

 SELECT ID, Name, max(EventTime), State FROM MyTable GROUP BY ID 

Answer 1

In databases that support analytic functions, you can 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 

Answer 2

 SELECT ID, Name, EventTime, State FROM MyTable mt WHERE EventTime = (SELECT MAX(EventTime) FROM MyTable sq WHERE mt.ID = sq.ID) 

Answer 3

You did not specify which database you are using, but you should be able to use the aggregate function in the subquery to get the maximum event time for each identifier:

 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; 

See SQL Fiddle with Demo

Answer 4

You can try the following: -

 SELECT ID, Name, EventTime, State FROM mytable mm Where EventTime IN (Select MAX(EventTime) from mytable mt where mt.id=mm.id) 

SQL FIDDLE