select row based on date from duplicate

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 2005 Forums

Transact-SQL (2005)

select row based on date from duplicate

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-10-12 : 11:53:33

My table looks like below

from above table I need pick data like below

I.e. '3391284' has two records but pick last (newly) ADD_DT value record (8/14/2008')

'1691047' has 5 records and 4 has same last (newly) ADD_DT. here pick one record from 4 ADD_DTs. like 1691047 'Health,Safetym ....' 2/5/2008

What SQL should I write to get this these records?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-12 : 12:08:03

You don't mean "latest" - you mean "oldest". Am I right?

No, you're never too old to Yak'n'Roll if you're too young to die.

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-10-12 : 12:09:40

It mean, last ADD_DT date. Get from last ADD_DT data.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2009-10-12 : 15:09:47

-- Setup test environment
DECLARE @MyTable
TABLE
(
[U##CONSTIT_ID] INT,
CODE_DESC VARCHAR(50),
ADD_DT DATETIME
)

INSERT @MyTable
SELECT 3454445, 'Drilling and Completions', '9/26/09 2:33 AM'
UNION ALL
SELECT 3454444, 'Drilling and Completions', '9/26/09 2:29 AM'
UNION ALL
SELECT 3404825, 'Production and Operations', '9/26/09 12:15 AM'
UNION ALL
SELECT 3391284, 'Drilling and Completions', '8/14/08'
UNION ALL
SELECT 3391284, 'Production and Operations', '9/7/07 10:16 AM'
UNION ALL
SELECT 1691047, 'Health, Safety, Security, Environment', '2/5/08'
UNION ALL
SELECT 1691047, 'Production and Operations', '2/5/08'
UNION ALL
SELECT 1691047, 'Drilling and Completions', '2/5/08'
UNION ALL
SELECT 1691047, 'Projects, Facilities and Const', '2/5/08'
UNION ALL
SELECT 1691047, 'Reservoir Description and Dy', '2/5/08';

-- Select Result

with cte1
AS
(
select *, ROW_NUMBER() OVER(PARTITION BY [U##CONSTIT_ID] ORDER BY ADD_DT DESC, CODE_DESC) AS Sequence
FROM @MyTable
)
SELECT *
FROM cte1 c
WHERE c.Sequence = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources