Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL Return Max PriceID Of Row Pairs?

Posted on 2014-02-14
Medium Priority
397 Views
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
``````
0
Question by:WorknHardr
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 16

Expert Comment

ID: 39860916
SELECT * FROM <Your Table> Y
WHERE Y.priceID = ( SELECT MAX(priceID) FROM <Your Table Y1)
0

LVL 21

Expert Comment

ID: 39860956
SELECT LISTID, STATE, LISTNAME, CODE , NAME, MAX(PRICEID), DATE FROM TableNAme
GROUP BY LISTID, STATE, LISTNAME, CODE , NAME,  DATE
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39861221
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

Author Closing Comment

ID: 39861344

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.