Strauss Strauss - 6 months ago 10
SQL Question

Getting a row with two group by constraints

I have a table

TIMESTAMP ID Name
5/30/2016 11:45 1 Ben
5/30/2016 11:45 2 Ben
5/30/2016 23:15 2 Ben
5/30/2016 7:30 1 Peter
5/30/2016 6:05 1 Peter
5/30/2016 14:40 2 May
5/30/2016 1:05 1 May


Now, I need to get the MIN timestamp for each distinct Name.
Then if there are more than one MIN entry, choose the one with the MAX ID.

So the result should be

TIMESTAMP ID Name
5/30/2016 11:45 2 Ben
5/30/2016 6:05 1 Peter
5/30/2016 1:05 1 May


I tried using the query below:

SELECT MIN(TIMESTAMP),NAME FROM TBLSAMPLE WHERE TIMESTAMP BETWEEN TO_DATE('5/30/2016', 'MM/DD/YYYY' ) AND TO_DATE('5/30/2016', 'MM/DD/YYYY' ) + 1
GROUP BY NAME

and I could get the minimum time. But once I add in MAX(ID) the result return an entry that does not match any of the rows.

Your help are really appreciated.

Answer

You can do this with row_number():

select t.*
from (select t.*,
             row_number() over (partition by name order by timestamp asc, id desc) as seqnum
      from tblsample t
     ) t
where seqnum = 1;

Your question doesn't specify a condition on the dates. But if you want to add a where clause, then add it to the subquery.