Oracle SQL: return only max value

Question

The below query results in 2 rows. I added the last 'and' statement in order to only show the result where the height was the max height but, the report still returns 2 rows?

select ds.catnr, pd.part_no, pd.description, ds.packtyp, pd.qty_box, ds.planqty, ds.weight_g, 
round((ds.planqty/pd.qty_box),'1') X, bom.av_part_no, lg.height, lg.length, lg.spine width, lg.part_no, lg.description
from oes_delsegview ds, part_description pd, 
TABLE(leos_flatbom_pkg.GetFlatBOM(pd.part_no)) bom, 
leos_gen_part_picture lg
where ds.ordnr = '0021565475'
and ds.posnr = '00002'
and ds.segnr = '001'
and ds.catnr = pd.catnr
and ds.prodtyp = pd.prodtyp
and ds.packtyp = pd.packtyp
and bom.prodtyp = 'KT'
and bom.av_part_no = lg.part_no
and lg.height = (select max(lg1.height) from leos_gen_part_picture lg1 where lg.part_no = lg1.part_no)

Why does the 'height = max(height)' not restrict my result and how can I change the script to only show the row where 'height = max(height)'?

Current result...

Required result...

Thanks

How many rows does it return without the last condition? Also 2? If yes, then the reason why is that you are comparing height of a particular row with it's max(height) since you are retrieving it for every part_no (where clause in inner select statement). — Kamil Gosciminski
– Kamil Gosciminski, Commented Nov 23, 2015 at 10:19
I can't see any output, so which two lines are you talking about? Also, they are rows not lines. Also, you might have more than one row with same MAX height. — Lalit Kumar B
– Lalit Kumar B, Commented Nov 23, 2015 at 10:20
I get 2 rows when not using the last condition, heights are each row are different, result added to question — SMORF
– SMORF, Commented Nov 23, 2015 at 10:45

LoztInSpace · Accepted Answer · 2015-11-23 10:19:50Z

2

Impossible to tell for sure without the data but I'd suggest

1) You have not got the same conditions on the max as the rest of the query

2) There are two rows with the same height

answered Nov 23, 2015 at 10:19

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

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

3 Comments

SMORF Over a year ago

heights are each row are different, result added to question

SMORF Over a year ago

got it ... you were correct, I missed one of the condition on the max as the rest of the query. Max condition amended to ... and lg.height = (select max(lg1.height) from TABLE(leos_flatbom_pkg.GetFlatBOM(pd.part_no)) bom1, leos_gen_part_picture lg1 where bom1.prodtyp = 'KT' and bom1.av_part_no = lg1.part_no)

LoztInSpace Over a year ago

Glad you sorted it out and happy I could help

Collectives™ on Stack Overflow

Oracle SQL: return only max value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related