Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exclude duplicates, keep the value with the latest date


Exclude duplicates, keep the value with the latest date

Author
Message
5280_Lifestyle
5280_Lifestyle
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 325
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"
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 17024
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)
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5008
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/
BriPan
BriPan
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 296
but If performance is in concern then go for

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


ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9018 Visits: 19030
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
5280_Lifestyle
5280_Lifestyle
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 325
Sean's solution worked. Thank you very much!

"Nicholas"
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 17024
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search