Exclude duplicates, keep the value with the latest date

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Exclude duplicates, keep the value with the...

Exclude duplicates, keep the value with the latest date

Rate Topic

Display Mode

Topic Options

Author

Message

5280_Lifestyle

Posted Monday, February 25, 2013 4:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:38 PM
Points: 39, Visits: 225

I'm trying to create a query with multiple columns. The CandidateID column includes values that appear more than once. I need my query to return the latest CandidateID value and to exclude the older CandidateID values of the same ID number.

Sample values:

200843	2012-02-14 07:49:00
200843	2012-02-15 06:56:00
231022	2012-02-14 07:49:00
233525	2012-01-30 21:15:00
276699	2012-01-30 21:14:00
291019	2012-01-30 21:15:00
298805	2012-01-30 21:15:00
298805	2012-02-15 06:56:00
298805	2012-02-21 09:39:00
306011	2012-02-14 07:49:00
306011	2012-02-15 06:56:00
306011	2012-02-21 09:55:00
313538	2012-01-30 21:15:00
316135	2012-01-30 21:14:00

I need the query to return the following...

200843	2012-02-15 06:56:00
231022	2012-02-14 07:49:00
233525	2012-01-30 21:15:00
276699	2012-01-30 21:14:00
291019	2012-01-30 21:15:00
298805	2012-02-21 09:39:00
306011	2012-02-21 09:55:00
313538	2012-01-30 21:15:00
316135	2012-01-30 21:14:00

"Nicholas"

Post #1423816

Sean Lange

Posted Monday, February 25, 2013 6:00 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547, Visits: 8,204

5280_Lifestyle (2/25/2013)

Like this?

select CandidateID, max(YourDateColumn)
from YourTable
group by CandidateID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1423832

Kingston Dhasian

Posted Tuesday, February 26, 2013 12:50 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:54 AM
Points: 2,202, Visits: 4,156

You can also use ROW_NUMBER()

SELECT	*
FROM	(
		SELECT	ROW_NUMBER() OVER ( PARTITION BY CandidateID ORDER BY YourDateColumn DESC ) AS RN, *
		FROM	YourTable
	) AS YT
WHERE	YT.RN = 1

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #1423912

BriPan

Posted Tuesday, February 26, 2013 3:39 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 01, 2013 6:16 AM
Points: 81, Visits: 286

but If performance is in concern then go for

select CandidateID, max(YourDateColumn)
from YourTable
group by CandidateID

Post #1423974

ChrisM@Work

Posted Tuesday, February 26, 2013 3:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 5,602, Visits: 10,953

BriPan (2/26/2013)

but If performance is in concern then go for

select CandidateID, max(YourDateColumn)
from YourTable
group by CandidateID

Sean's solution is almost certainly quicker than Kingston's ROW_NUMBER method; however, Kingston's solution is a viable alternative which introduces the method to those who may not already be familiar with it - and of course, it demonstrates how to identify the target rows without necessarily filtering out those which are not targeted.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1423981

5280_Lifestyle

Posted Tuesday, February 26, 2013 8:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:38 PM
Points: 39, Visits: 225

Sean's solution worked. Thank you very much!

"Nicholas"

Post #1424097

Sean Lange

Posted Wednesday, February 27, 2013 7:56 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:46 AM
Points: 8,547, Visits: 8,204

5280_Lifestyle (2/26/2013)

Sean's solution worked. Thank you very much!

You are quite welcome. Glad that worked for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1424550

« Prev Topic | Next Topic »

Permissions