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

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

Gordon Linoff · Accepted Answer · 2021-01-25 14:02:20Z

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;

answered Jan 25, 2021 at 14:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

realopa Over a year ago

Thanks! I used the first one it helped! And I wasn't even aware of over function. Definitely going to try that in future! Thanks for the tip

Mahamoutou · Accepted Answer · 2021-01-25 14:04:02Z

1

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

answered Jan 25, 2021 at 14:04

Mahamoutou

1,5651 gold badge7 silver badges12 bronze badges

1 Comment

realopa Over a year ago

Thanks! You're the best!

Collectives™ on Stack Overflow

Oracle SQL: MAX function in WHERE clause

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related