SQL Return Max PriceID Of Row Pairs?

I need to Select 1 row from each row/pair having Max(PriceID) and return 1 row if only 1 row is found (non-pair).

For example:
   1400      OH              Price List 4      101              Hammer       26              12.50      2014-12-1
   1400      OH              Price List 4      320              Pliers       10                9.25      2014-12-1
   1400      OH              Price List 4      221              Drill               30              39.99      2014-12-1

[Table Data]
ListID	State	ListName	Code	Name	 PriceID	Price	  Date	
1400	OH	        Price List 4	101	        Hammer	 26	        12.10	2014-12-1	
1400	OH	        Price List 4	101	        Hammer	 24	        12.20	2014-12-1	
1400	OH	        Price List 4	320	        Pliers	 10	          9.25	2014-12-1	
1400	OH	        Price List 4	320	        Pliers	 8	          9.15	2014-12-1	
1400	OH	        Price List 4	221	        Drill	         30	        39.99	2014-12-1	

Open in new window

WorknHardrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this article explains the "issue" and the path for solutions for this kind of issue:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
Surendra NathTechnology LeadCommented:
SELECT * FROM <Your Table> Y
WHERE Y.priceID = ( SELECT MAX(priceID) FROM <Your Table Y1)
0
 
Alpesh PatelAssistant ConsultantCommented:
SELECT LISTID, STATE, LISTNAME, CODE , NAME, MAX(PRICEID), DATE FROM TableNAme
GROUP BY LISTID, STATE, LISTNAME, CODE , NAME,  DATE
0
 
WorknHardrAuthor Commented:
Great link, will bookmark, thx

Solution:
    Select t1.*
    From #Temp2 t1
    Where t1.PriceID = ( Select Max(t2.PriceID)
                              From #Temp2 t2
                                        Where t2.Code = t1.Code and t2.Name = t1.Name)
    Order By Code, Name Asc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.