my aggregated query needs help

brgdotnet
brgdotnet used Ask the Experts™
on
I have a query that queries the table below.  The query displays each employee, and the maximum price which they have
paid for a lunch item. They query works fine, but I would also like to display the name of the ProductName to display the name of the product which was purchased. If I try to include the ProductName in my query, it messes up my aggregation.
Can anyone tell me how the query can be changed to also display the name of the product? I have attached the sql to recreate and populate he sample database shown below, in case someone wants to recreate it.
My query is shown below in Bold.

Table : PurchaseInfo


ProductId      |      ProductName   | StickerPrice    | EmployeeName
--------------------------------------------------------------------------------------------------
001             |      Bacon Soup      |      4.00            | RayJefferson
002             |      Coffee                 | 7.00                    | JamisHiggins
003             |      Ice Cream         |      5.00            | JamisHiggins
004             |      Lobster Soup   |      18.00            | RayJefferson
005             |      Prime Rib          | 18.00            | AmandaNunez
006             |      Chiffon Cake    | 20.00            | JamisHiggins



select EmployeeName,MAX(StickerPrice) AS PRICE FROM PurchaseInfo GROUP BY EmployeeName


Result from query above:

EmployeeName  |  PRICE
-------------------------------------
AmandaNunez   |      18
JamisHiggins       |      20
RayJefferson       |      18
SampleDatabase.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
window functions to the rescue!
SELECT
    EmployeeName
  , StickerPrice
  , ProductName
FROM (
    SELECT
        EmployeeName
      , MAX( StickerPrice ) OVER (PARTITION BY EmployeeName) AS max_price
      , StickerPrice
      , ProductName
    FROM PurchaseInfo
) d
WHERE StickerPrice = max_price

Open in new window

Using max() over(...) you get that max value repeated on each row, so then it is easy to get all columns where the stickerprice = the maximum price
brgdotnetcontractor

Author

Commented:
Thanks. Man you are incredible Paul. I am going to really study this query. Thanks!
Hamed NasrRetired IT Professional
Commented:
Another way using Access like query.
SELECT EMP.EmployeeName, EMP_PROD.ProductName,EMP_PROD.Price FROM (select EmployeeName,MAX(StickerPrice) AS PRICE FROM PurchaseInfo GROUP BY EmployeeName) AS EMP
INNER JOIN (select EmployeeName,ProductName,MAX(StickerPrice) AS PRICE FROM PurchaseInfo GROUP BY ProductName,EmployeeName) AS EMP_PROD
ON EMP.EMPLOYEENAME=EMP_PROD.EmployeeName AND EMP.Price=EMP_PROD.Price

Open in new window

brgdotnetcontractor

Author

Commented:
Thanks Nasr. So cool. You guys are amazing. I will study this one too.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial