August 28, 2009 at 1:41 pm
Hello,
I would like to extract from our Database the highest valued item purchased for each customer. Example
CustNoItemNoSalesPrice
ABC15
ABC48
ABC26
XYZ10125
XYZ22148
The SQL should extract only two lines.
Line 2 customer ABC, ItemNo = 4, and SalesPrice = 8
Line 5 customer XYZ, ItemNo = 22 and salesprice = 148
I have tried the MAX function. However the MAX function requires grouping. I always get the same 5 records. How can I select just the MAX values for each Customer while retaining the Customer Number , Item Number and SalesPrice
Regards
August 28, 2009 at 2:18 pm
What would you want to see if more than one item for a customer sold for the same max price?
August 28, 2009 at 3:47 pm
Since you didn't follow etiquette by providing DDL and insert scripts for your sample, I used the AdventureWorks database. If you want with ties, you need to use the Rank function, but if you want a single row you want the Row_Number function. My solution shows both functions, but uses the Rank.
WITH RankedRows AS (
SELECT
CustomerID
, OrderDate
, TotalDue
, Row_Number() OVER( PARTITION BY CustomerID ORDER BY TotalDue DESC) AS RowNum
, Rank() OVER( PARTITION BY CustomerID ORDER BY TotalDue DESC) AS RankNum
FROM Sales.SalesOrderHeader
)
SELECT *
FROM RankedRows
WHERE RankNum = 1
You may need to provide additional sort columns in the CTE in order to break ties when using Row_Number.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 29, 2009 at 7:57 pm
SELECT CustNo, ItemNo, SalesPrice
FROM UrTable mauntable
INNER JOIN (SELECT CustNo, MAX(SalesPrice) FROM UrTable GROUP BY CustNo, ItemNo) maxSale ON (mauntable.CustNo = maxSale.CustNo AND mauntable.SalesPrice= maxSale.SalesPrice)
August 30, 2009 at 6:26 am
Thanks for both of your suggestions. They work perfectly. Thanks for your help once again. This forum rocks
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply