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
– Kaushik Nayak, Commented Mar 7, 2018 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
– Sam King, Commented Mar 7, 2018 at 13:46

Gordon Linoff · Accepted Answer · 2018-03-07 12:40:17Z

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 7, 2018 at 12:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MT0 Over a year ago

You do not need to order by status in the min ... keep query as the min(status) part is already performing the same function.

Sam King Over a year ago

Thanks! I went with the second option, which works a treat

ScaisEdge · Accepted Answer · 2018-03-07 12:42:31Z

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 7, 2018 at 12:42

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Constructing an Oracle SQL query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related