Qry help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Qry help

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2009-07-20 : 15:42:15

If we have multiple rows for a same ticker, then I want to select only the row which has the Max(SecurityListingID) - 28280 - not using cursors

Sample data:

Ticker SecurityID SecurityListingID
------------------ ----------- -----------------
WRS 13574 10589
WRS 31460 28280

O/P

Ticker SecurityID SecurityListingID
------------------ ----------- -----------------
WRS 31460 28280

Thx
Venu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 15:47:05

SELECT Ticker, SecurityID, SecurityListingID
FROM (
SELECT Ticker, SecurityID, SecurityListingID, ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY SecurityListingID) AS recID
FROM Table1
) AS d
WHERE recID = 1

N 56°04'39.26"
E 12°55'05.63"

avmreddy17
Posting Yak Master

180 Posts

Posted - 2009-07-20 : 17:06:44

Thanks Peso. I just added ORDER BY SecurityListingID DESC as I wanted to pick up the MAX SecurityListingID

SELECT Ticker, SecurityID, SecurityListingID
FROM (
SELECT Ticker, SecurityID, SecurityListingID, ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY SecurityListingID DESC) AS recID
FROM Table1
) AS d
WHERE recID = 1

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-20 : 23:26:55

Hi try this...

CREATE TABLE #temp
             (
                          ID                INT IDENTITY(1,1),
                          Ticker            VARCHAR(3)       ,
                          SecurityID        INT              ,
                          SecurityListingID INT
             )
INSERT	INTO   #temp
SELECT 'WRS','13574','10589'UNION ALL
SELECT 'WRS','31460','28280'

SELECT *
FROM   #temp
WHERE  SecurityListingID IN
       (SELECT  MAX(SecurityListingID)
       FROM     #TEMP
       GROUP BY Ticker
       )
DROP TABLE #temp

-------------------------
R..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 03:17:42

quote:

Originally posted by rajdaksha

Hi try this...

CREATE TABLE #temp
             (
                          ID                INT IDENTITY(1,1),
                          Ticker            VARCHAR(3)       ,
                          SecurityID        INT              ,
                          SecurityListingID INT
             )
INSERT	INTO   #temp
SELECT 'WRS','13574','10589'UNION ALL
SELECT 'WRS','31460','28280'

SELECT *
FROM   #temp
WHERE  SecurityListingID IN
       (SELECT  MAX(SecurityListingID)
       FROM     #TEMP
       GROUP BY Ticker
       )
DROP TABLE #temp

-------------------------
R..

Again. This is not reliable. Refer my reply in the other thread

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources