Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exclude duplicates, keep the value with the latest date Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 4:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
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
Posted Monday, February 25, 2013 6:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 13,253, Visits: 12,087
5280_Lifestyle (2/25/2013)
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.



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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1423832
Posted Tuesday, February 26, 2013 12:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:30 AM
Points: 2,631, Visits: 4,722
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
Posted Tuesday, February 26, 2013 3:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
but If performance is in concern then go for

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

Post #1423974
Posted Tuesday, February 26, 2013 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Posted Tuesday, February 26, 2013 8:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
Sean's solution worked. Thank you very much!

"Nicholas"
Post #1424097
Posted Wednesday, February 27, 2013 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 13,253, Visits: 12,087
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1424550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse