How to select only one record from duplicates

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

Transact-SQL (2012)

How to select only one record from duplicates

Author

Topic

Gerdis
Starting Member

1 Post

Posted - 2013-07-30 : 06:25:36

I have a table with duplicated records (well technically they all are disitnct records with some variations) and I need to select the only record which has latest DATETIMEACHIVED value. How do I do this?

System_ID | PARCEL_ID | Wheight | DATETIMEACHIVED
----------------------------------------------------
1 | 1 | 0.5 | 01-01-2012
1 | 1 | 0.25 | 02-01-2012
1 | 1 | 0.51 | 03-01-2012
1 | 1 | 0.26 | 05-01-2012
1 | 1 | 0.56 | 04-01-2012
1 | 1 | 0.25 | 06-01-2012
1 | 8 | 1.36 | 13-02-2010
1 | 8 | 1.36 | 14-02-2010
2 | 1 | 30.15 | 29-06-2013
2 | 1 | 28 | 29-07-2013
2 | 1 | 21 | 29-08-2013

If selected correctly I would have a result

System_ID | PARCEL_ID | Wheight | DATETIMEACHIVED
------------------------------------------------------
1 | 1 | 0.25 | 06-01-2012
1 | 8 | 1.36 | 14-02-2010
2 | 1 | 21 | 29-08-2013

Anyones help on this would be greatly appreciated

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-30 : 06:29:43

[code]SELECT
System_ID,
Parcel_ID,
Wheight,
DATETIMEACHIVED
FROM
(
SELECT
System_ID,
Parcel_ID,
Wheight,
DATETIMEACHIVED,
ROW_NUMBER() OVER(PARTITION BY System_ID, PARCEL_ID
ORDER BY DATETIMEACHIVED DESC) AS RN
FROM
YourTable
) s
WHERE RN = 1;[/code]
If you can have duplicates, i.e., more than one row for a given combination of System_ID and Parcel_ID with the same max date, and if you want to get all such duplicates, use RANK() instead of ROW_NUMBER()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources