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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to select only one record from duplicates
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gerdis
Starting Member

1 Posts

Posted - 07/30/2013 :  06:25:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 07/30/2013 :  06:29:43  Show Profile  Reply with Quote
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;

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()
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000