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!

Give a proper title to the question that indicates the problem. "Constructing a query" is too general. — Kaushik Nayak, Mar 07 '18 at 12:49
If you can suggest a better title, I will happily change it (if I can). Part of the problem was not knowing the technical name for what I was trying to do — Sam King, Mar 07 '18 at 13:46

score 3 · Accepted Answer · answered Mar 07 '18 at 12:40

3

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;

answered Mar 07 '18 at 12:40

Gordon Linoff

1,242,037
58
646
786

You do not need to order by `status` in the `min ... keep` query as the `min(status)` part is already performing the same function. – MT0 Mar 07 '18 at 12:50
1

Thanks! I went with the second option, which works a treat – Sam King Mar 07 '18 at 13:45

score 0 · Answer 2 · answered Mar 07 '18 at 12:42

0

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

answered Mar 07 '18 at 12:42

ScaisEdge

131,976
10
91
107

Constructing an Oracle SQL query

2 Answers2