SQL Server Forums
Profile | Register | 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
 New Topic  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

3563 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  
 New 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