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.

Answer 1

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.

Answer 2

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