CODEWITHSUNDEEP
sqloracle-database
jackie21
jackie21

Reputation: 337

Using max with conditions ORACLE SQL

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

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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')
                   );

Upvotes: 1

Related Questions