Select distinct rows whilst grouping by max 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

What I need is the state for each distinct userid from the last eventtime 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

possible duplicate of [SQL server select distinct rows using most recent value only](http://stackoverflow.com/questions/3442931/sql-server-select-distinct-rows-using-most-recent-value-only) — Bulat, Aug 12 '15 at 08:33

score 11 · Answer 1 · answered Jul 23 '13 at 15:50

11

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

answered Jul 23 '13 at 15:50

fancyPants

50,732
33
89
96

1

Although I much prefer `row_number()`, this might perform the best under some circumstances. – Gordon Linoff Jul 23 '13 at 15:54
Also this is standard SQL :) Row_number() is not available everywhere. – fancyPants Jul 23 '13 at 15:56
2

. . `row_number()` *is* ANSI standard SQL. The problem is, no real database really implements the standard. – Gordon Linoff Jul 23 '13 at 16:00

score 7 · Accepted Answer · answered Jul 23 '13 at 15:50

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

score 2 · Answer 3 · answered Jul 23 '13 at 15:51

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;

See SQL Fiddle with Demo

Vivek Sadh · Answer 4 · 2013-07-23T16:22:53.153

2

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)

SQL FIDDLE

edited Jul 23 '13 at 16:22

answered Jul 23 '13 at 15:56

Vivek Sadh

4,230
3
32
49

This 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. – Hart CO Jul 23 '13 at 16:01
@Goat CO You are absolutely right bro. I got what you said. I have updated it and its working fine now. :) – Vivek Sadh Jul 23 '13 at 16:23
Yeah 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 of `IN` because the subquery returns one value per ID. – Hart CO Jul 23 '13 at 17:22

Select distinct rows whilst grouping by max value

4 Answers4

Linked