Solved

SQL Return Max PriceID Of Row Pairs?

Posted on 2014-02-14
4
384 Views
Last Modified: 2014-02-15
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

0
Comment
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
  • Learn & ask questions
4 Comments
 
LVL 16

Expert Comment

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

Expert Comment

by:Alpesh Patel
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

by:
Guy Hengel [angelIII / a3] earned 500 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

by:WorknHardr
ID: 39861344
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question