Finding the second highest value - HOW? 1

[robertsfd]

I have a table with the following structure:

Sales(CustomerID, Name, Date, InventoryName, Amount, CheckNumber)

This is a table with every sale for a number of years, containing a customer's ID, their name, the date of the sale, what they bought, the amount they paid, and their check number.

I want to find the largest and second largest amount for each customer.

To find the largest amount for each, I do the following:
SELECT CustomerID, Max(Amount) AS MaxAmount
FROM Sales
GROUP BY CustomerID;

But I cannot figure out how to find the second largest amount. Anyone know how to do this? Thanks!

 

[r937]

you can use a crrelated subquery for this

[tt]select CustomerID
, max(Amount) as SecondMaxAmount
from Sales S
where Amount <
( select max(AMount)
from Sales
where CustomerID = S.CustomerID )
group
by CustomerID[/tt]

rudy

http://r937.com/

 

[robertsfd]

Ah, thanks! Another problem: I tried to simplify my example to ease getting an answer and to allow me to see if I could extrapolate into my more complicated example. Needless to say, I could not. Can you troubleshoot this example?

I have the following four tables:

VENDOR(VendorID, VendorName)
PURCHASE(PONumber, Date, VendorID)
PURCH-INVEN(PONumber, InventoryID, Quantity, UnitPrice)
INVENTORY(InventoryID, InventoryName)

The first is a table of vendors from whom I purchase inventory; the second is a table of purchase orders; the fourth is a table of all the inventory items I purchase; the third is the line item detail of each purchase order (to break up the many to many relationship between purchase and inventory).

I want to find the largest and second largest purchase order to each vendor so I can calculate the ratio of largest to second largest.

I took a four step approach:

Step 1: calculate totals for each purchase order (query #1 called PurchaseOrderTotals)

SELECT Vendor.VendorID, Purchase.PONumber, Sum([Purch-Inven].Quantity*[Purch-Inven].UnitPrice) AS TotalPurchase
FROM Purchase, [Purch-Inven], Vendor
WHERE Purchase.PONumber=[Purch-Inven].[PONumber]
AND Purchase.VendorID = Vendor.VendorID
GROUP BY Vendor.VendorID, Purchase.PONumber;

Step 2: calculate the max PO totals from step 1 grouped by vendor (query #2 named MaxPOs)

SELECT Vendor.VendorID, Max(PurchaseOrderTotals.TotalPurchase) AS MaxPO
FROM PurchaseOrderTotals, Vendor
WHERE PurchaseOrderTotals.VendorID = Vendor.VendorID
GROUP BY Vendor.VendorID;

Step 3: find the second largest
THIS IS WHAT I COULD NOT FIGURE OUT.

Step 4:
Didn't get this far.

The first two steps work as planned, the third I cannot figure out. Know how to do this? Thanks!

 

[jdbass]

You could also use the Select Top capability...

SELECT TOP 2 tblScheduleMetrics.SqFtPerPallet
FROM tblScheduleMetrics
GROUP BY tblScheduleMetrics.SqFtPerPallet
ORDER BY tblScheduleMetrics.SqFtPerPallet DESC;

 

[r937]

since you want &quot;the largest and second largest purchase order to each vendor so I can calculate the ratio of largest to second largest&quot; then definitely go with the TOP 2 approach

however, it's a bit tricky, because you want the top 2 POs per vendor, so that requires a correlated subselect

i'll use your first query, it's quite handy

[tt]select VendorID
, PONumber
, TotalPurchase
from PurchaseOrderTotals POT
where TotalPurchase in
( select top 2 TotalPurchase
from PurchaseOrderTotals
where VendorID = POT.VendorID
order by TotalPurchase )[/tt]

rudy

 

[dest1nyseeker]

I found this site helpful in a similar situation:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/inAc.asp

 

[robertsfd]

Thanks for all the help. I couldn't get the query to work, however, I did get to work what I wanted. The referenced article about finding second highest value for groups hit the nail on the head.

Thanks for all the help anyhow. It was a learning experience nonetheless.