CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Sam King
Sam King

Reputation: 90

Constructing an Oracle SQL query

Given a data table like:

+-----+-------------+--------+
| id  | end         | status |
+-----+-------------+--------+
| a   | 07-FEB-2018 | 1      |
| a   | 08-FEB-2018 | 2      |
| a   | 08-FEB-2018 | 3      |
| b   | 06-MAR-2018 | 2      |
| b   | 08-SEP-2018 | 3      |
+-----+-------------+--------+

In Oracle SQL is it possible to construct a query that would return, for each id, the minimum status for the maximum end date (there may be multiple rows with the same maximum end date).

So in this example, this would be returned :

+-----+--------+
| id  | status |
+-----+--------+
| a   | 2      |
| b   | 3      |
+-----+--------+

I've been looking at FIRST_VALUE and PARTITION BY, but I'm struggling to understand how they work and if they're what I need anyway and as a result I'm not really getting anywhere.

Any help would be greatly appreciated!

Upvotes: 1

Views: 54

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

You could use an inner join othe max end 

select m.id, min(m.status)
from my_table m
inner join ( 
select id, max(end) max_end
from my_table  
group by id ) t.max_end = m.end   and t.id = m.id 
group by m.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One method uses row_number():

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

You can do this with aggregation functions as well:

select id,
       min(status) keep (dense_rank first order by date desc, status asc) as status
from t
group by id;

Upvotes: 3

Related Questions