my aggregated query needs help

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
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brgdotnetcontractorAuthor Commented:
Thanks. Man you are incredible Paul. I am going to really study this query. Thanks!
Hamed NasrRetired IT ProfessionalCommented:
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

brgdotnetcontractorAuthor Commented:
Thanks Nasr. So cool. You guys are amazing. I will study this one too.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.