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)

SQL FIDDLE

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;

See SQL Fiddle with Demo

Share:
14,459
lethalMango
Author by

lethalMango

Updated on June 26, 2022

Comments

  • lethalMango
    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 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
    
  • Gordon Linoff
    Gordon Linoff over 10 years
    Although I much prefer row_number(), this might perform the best under some circumstances.
  • fancyPants
    fancyPants over 10 years
    Also this is standard SQL :) Row_number() is not available everywhere.
  • Gordon Linoff
    Gordon Linoff over 10 years
    . . row_number() is ANSI standard SQL. The problem is, no real database really implements the standard.
  • Hart CO
    Hart CO over 10 years
    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.
  • fancyPants
    fancyPants over 10 years
    Oh really? Okay, didn't know that. Thanks for the info.
  • Vivek Sadh
    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
    Hart CO over 10 years
    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.