We help IT Professionals succeed at work.

my aggregated query needs help

brgdotnet
brgdotnet asked
on
78 Views
Last Modified: 2018-12-29
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

EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
brgdotnetcontractor

Author

Commented:
Thanks. Man you are incredible Paul. I am going to really study this query. Thanks!
Hamed NasrRetired IT Professional
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
brgdotnetcontractor

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.