Help with SQL (return 2nd/3rd top answers using SUM??)

[jacy]

Hi,

I need to find out the supplier name of the suppliers who supply the second and third largest total quantity of parts (using 2 different SQL statements). If possible I need the SQL to just return a single supplier name (not a list of suppliers in order of Qty - I need to avoid using MoveNext in a recordset)

My tables are:
Supplier (S#, SupplierName)
Part(P#, PartName)
Job(J#, City)
SPJ(S#, P#, J#, Qty)

Thanks

 

[link9]

Supplier (S#, SupplierName)
Part(P#, PartName)
Job(J#, City)
SPJ(S#, P#, J#, Qty)

SELECT top 3 S#, SUM(Qty) AS total FROM SPJ GROUP BY S# ORDER BY total

should fix you right up, returning the three highest quantities along with the supplier number. You could use a JOIN statement if you would rather have the actual supplier's name returned instead of the S#


Paul Prewett

 

[jacy]

thanks... so that would return say
S1 10
S2 9
S3 8

If i wanted to get straight to S3 as the third top supplier, could i use the above query and inverse it somehow, and do top 1, just to get one value returned of the 3rd top supplier?

thanks a lot for answering

 

[JerryDennison]

While it may be possible to do this in a single query it would be very easy to simply use this query as the basis for another one grouped by S# and Min the Top Seller.

 

[jacy]

of course! thanks a lot