Constructing an Oracle SQL query

Question

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!

Answer 1

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;