Oracle SQL: MAX function in WHERE clause

user_8806639 0
Viewed 3776

I've always received very helpful tips from this site and I'm very grateful for all of those so once again I'll try my luck!

I'm trying to use max function in where clause in order to fetch the latest revision of change order. So in table I have fields order_no, line_no, chg_order_no (revision number) and amount. So whenever user modifies purchase order then the system creates new change order and order_no and line_no will remain same but chg_order_no and amount changes. Note that chg_order_no can be anything from 1 to e.g. 100

order_no line_no chg_order_no amount
order1 1 1 100
order2 1 1 250
order1 1 2 300

Now I only want to fetch following rows:

order_no line_no chg_order_no amount
order2 1 1 250
order1 1 2 300

I have tried to use following select query but it doesn't seem to work for all orders

SELECT co.order_no, co.chg_order_no,co.line_no, co.amount 
FROM CHANGE_ORDER co 
WHERE co.chg_order_no = (select MAX(chg_order_no) from CHANGE_ORDER)

Thanks in advance

max where-clause oracle sql
2 Answers

You can use this method, but you need a correlated subquery:

SELECT co.order_no, co.chg_order_no,co.line_no, co.amount 
FROM CHANGE_ORDER co 
WHERE co.chg_order_no = (select MAX(co2.chg_order_no)
                         from CHANGE_ORDER co2
                         where co.order_no = co2.order_no
                        );

Your version returns the maximum chg_order_no over all the data. However, you only want it for each order_no.

There are many ways to accomplish this. But Oracle has new functionality that lets you avoid a subquery:

select co.*
from change_order co
order by row_number() over (partition by order_no order by chg_order_no desc)
fetch first 1 row with ties;
user_11440350

Add just this where clause in the subquery : where order_no = co.order_no

SELECT co.order_no, co.chg_order_no,co.line_no, co.amount 
FROM CHANGE_ORDER co 
WHERE co.chg_order_no = (select MAX(chg_order_no) from CHANGE_ORDER where order_no = co.order_no)
;
user_136191160
Related