ORACLE SQL find row with max date for each grouping

Question

I am trying to write a query which will return only the rows, which time has the greatest value for each id

Table: positions
id          time       otherCols...
---------- ----------- ----------
1           1     
1           2      
3           1      
1           3      
2           1       
3           2

Result should look like:

id          time       otherCols...
---------- ----------- ----------      
1           3      
2           1       
3           2

I tried grouping by id but I don't know how to sort after that and pick only the top result.

Barbaros Özhan · Answer 1 · 2020-09-26T18:35:01.733

3

You can use MAX(..) KEEP (DENSE_RANK ..) OVER (PARTITION BY ..) analytic function without need of any subquery :

SELECT MAX(time) KEEP (DENSE_RANK LAST ORDER BY time) 
                 OVER (PARTITION BY id) AS time_max,
       p.*
  FROM positions p
 ORDER BY id

Demo

edited Sep 26 '20 at 18:35

answered Sep 26 '20 at 16:13

Barbaros Özhan

59,113
10
31
55

Why not just use `GROUP BY`? – Gordon Linoff Sep 26 '20 at 16:17
The OP wants an indeterminate number of extra columns. For one or two, I would suggest `keep`, but for a general query there are other methods that are much less cumbersome to write. – Gordon Linoff Sep 26 '20 at 16:30
Yes, I want just to filter out, columns should stay the same. – boneash Sep 26 '20 at 18:27
OK @boneash I understand now, then just need to add the other columns along with this analytic function. – Barbaros Özhan Sep 26 '20 at 18:39

Gordon Linoff · Accepted Answer · 2020-09-26T16:08:53.540

2

You can use window functions:

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

An alternative method is a correlated subquery:

select t.*
from t
where t.time = (select max(t2.time) from t t2 where t2.id = t.id);

This is different from the first query in two respects:

If there are duplicate times for an id, then this returns all rows for an id. You can get that behavior using rank() in the first query.
This will not return NULL id values or ids where the time is uniformly NULL. The first query does.

edited Sep 26 '20 at 16:08

answered Sep 26 '20 at 15:53

Gordon Linoff

1,242,037
58
646
786

Thanks for the queries, looks like second is much faster but they give different number of results. – boneash Sep 26 '20 at 16:05
@boneash . . . That would suggest that you have duplicate times for a given `id`. – Gordon Linoff Sep 26 '20 at 16:09

ORACLE SQL find row with max date for each grouping

2 Answers2