Need Help with Simple SQL

[TStriker]

Hi Folks,

I need help with a simple query. I have a view called PARTS. It contains these columns:

PART_NO, BIN, QTY

What I want to do is return a list that shows the PART_NO and BIN that contains the greatest quantity.

Example:
123456 ABC 10
123456 XYZ 20
123456 DEF 30
123456 ZZZ 30
987654 AAA 10

In the example above I would like it to return:

123456 ZZZ
987654 AAA

1st reason: Because 30 is the greatest of the QTYs for part 123456
2nd reason: because 'ZZZ' is greater than 'DEF' (when the QTY is equal)

And of course return 987654 10 because it is the highest qty because there are no other rows for that part number.

I'm not very adept at SQL. Can anybody help me out?

-Striker

 

[DotNetGnat]

SELECT partnum, partname, qty FROM mytable A
WHERE A.qty IN (select Top 1 qty from mytable B
WHERE A.partnum=B.partnum ORDER BY qty DESC)

-DNG

 

[PHV]

SELECT A.PART_NO, Max(A.BIN), A.QTY
FROM PARTS A INNER JOIN (
SELECT PART_NO, Max(QTY) MaxQ FROM PARTS GROUP BY PART_NO
) B ON A.PART_NO = B.PART_NO AND A.QTY = B.MaxQ
GROUP BY A.PART_NO, A.QTY

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LKBrwnDBA]

Or try this:

Select A.Part_No, A.Bin, A.Qty
  From Parts A 
 Where A.Qty = (
    Select Max(Qty) From Parts B
     Where A.Part_No = B.Part_No);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

DotNetGnat and LKBrwnDBA, you both missed the 2nd reason:
because 'ZZZ' is greater than 'DEF' (when the QTY is equal)

 

[DotNetGnat]

Good Catch PHV, i totally ignored...

thats the difference between an EXPERT(you) and the one who is learning stuff(me)

-DNG

 

[TStriker]

Well guys,

It's looking good. I got one of them to work but it wasn't the one from PHV. I'm using Oracle 8i and it doesn't like the "inner join" syntax so could you help me convert it?

TIA.

-Striker

 

[PHV]

You posted in the ANSI_SQL Forum, so you've got an ansi solution.
Next time feel free to post in one of the Oracle fora to get more accurate suggestions.

 

[DotNetGnat]

but which one is working so that we can modify it...

-DNG

 

[TStriker]

Sorry guys, looks like I posted to the wrong forum.

Anyway, according to PHV the others missed one of the criteria so I would like it applied to PHV's sample.

-Striker

 

[LKBrwnDBA]

Take a look at this:

Select A.Part_No, A.Bin, A.Qty
  From Parts A 
 Where A.Qty = (
    Select Max(Qty) From Parts B
     Where A.Part_No = B.Part_No)
   And A.Bin = (
    Select Max(Bin) From Parts B
     Where A.Part_No = B.Part_No);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb