CODEWITHSUNDEEP
sqloracle-databasegroup-byanalytic-functions
boneash
boneash

Reputation: 158

ORACLE SQL find row with max date for each grouping

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.

Upvotes: 1

Views: 6714

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 2

Related Questions