Oracle SQL: MAX function in WHERE clause

Question

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

question from:https://stackoverflow.com/questions/65886240/oracle-sql-max-function-in-where-clause

Answer 1

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;