Need help with Sequel statement.

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with Sequel statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-05-09 : 17:15:23

[code]
Hi,

I try to return only unique row with a Max date and wonder if you can help. Please see the business rule and output below.
I am using SQL 2008.

Thank you so much in advance.

IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE dbo.#Temp
(
patient_id INT NULL,
creator VARCHAR(25) NULL,
MaxDate DATETIME NULL
)
GO

-- DELETE #Temp

INSERT INTO dbo.#Temp([Patient_ID],[Creator],[MaxDate])
VALUES (1299619087, 'abcd\dtaylor', '04/03/2013' )

INSERT INTO dbo.#Temp([Patient_ID],[Creator],[MaxDate])
VALUES (1299619087, 'abcd\kmjala', '01/09/2013' )

INSERT INTO dbo.#Temp([Patient_ID],[Creator],[MaxDate])
VALUES (1299619087, 'abcd\kmjala', '04/03/2013' );

SELECT *
FROM #Temp
go

patient_id creator MaxDate
----------- ------------------------- -----------------------
1299619087 abcd\dtaylor 2013-04-03 00:00:00.000
1299619087 abcd\kmjala 2013-01-09 00:00:00.000
1299619087 abcd\kmjala 2013-04-03 00:00:00.000

-- Business rule: Return only unique creator with the Max date.

Desire output:

patient_id creator MaxDate
----------- ------------------------- -----------------------
1299619087 abcd\dtaylor 2013-04-03 00:00:00.000
1299619087 abcd\kmjala 2013-04-03 00:00:00.000[/code]

Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-09 : 17:35:56

[code]SELECT patient_id ,
creator ,
MaxDate
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY creator ORDER BY MaxDate DESC ) AS RN
FROM #Temp
) s
WHERE RN = 1;[/code]If you might have two (or more) rows with the same creator and same MaxDate which happens to be the latest date, and if you want to get both those rows, use RANK() instead of ROW_NUMBER()

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-05-09 : 20:11:00

Thank you Jame

quote:
Originally posted by James K

SELECT patient_id ,
creator ,
MaxDate
FROM ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY creator ORDER BY MaxDate DESC ) AS RN
FROM #Temp
) s
WHERE RN = 1;
If you might have two (or more) rows with the same creator and same MaxDate which happens to be the latest date, and if you want to get both those rows, use RANK() instead of ROW_NUMBER()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources