Using max with conditions ORACLE SQL

Question

I have a sequence of legs that make up a route like below:

route   node_start  leg_ship_method    node_end       sequence
xx      MAD4        bus                MAD8              1
xx      MAD8        train              MAD8              2
yy      BCN1        plane              BCN8              1
yy      BCN8        boat                                 2
xx      MAD8        train              DNW1              3
xx      DNW1        train                                4

I am interested in obtaining only the rows that relate to the MAX sequence id of the route. After checking the max sequence id, I would like to check if the node_start is IN ('MAD8',BCN8'). If not then I compute my MAX(sequence_id)-1. I do not need any further conditions. My expected output therefore is:

   route    node_start  leg_ship_method    node_end       sequence
    xx      MAD8        train              DNW1              3
    yy      BCN8        boat                                 2

My code is below but this gives me only the max of the route, but without the condition I mentioned above

SELECT y.* 
FROM 
       (
       SELECT
             y.route,
            max(sequence) max_sequence
       FROM test y
       GROUP BY
             1
       ) AS x
       INNER JOIN test y
       on x.route = y.route and y.sequence = x.max_sequence
       ) AS t

You'll need a recursive CTE, available in Oracle 11g Release 2 and newer. What version of Oracle do you have? — The Impaler
– The Impaler, Commented Jan 29, 2020 at 20:31

Ankit Bajpai · Accepted Answer · 2020-01-30 05:32:01Z

1

If I understand correctly, you can use a correlated subquery and filter for the end points you want before calculating the max():

select t.*
from t
where t.sequence = (select max(t2.sequence)
                    from t t2
                    where t2.route = t.route and
                          t2.node_start in ('MAD8', 'BCN8')
                   );

edited Jan 30, 2020 at 5:32

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

answered Jan 29, 2020 at 20:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Using max with conditions ORACLE SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related