InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 20:30:26 GMT20http://www.sqlservercentral.com/Forums/Topic1423816-1292-1.aspx[quote][b]5280_Lifestyle (2/26/2013)[/b][hr]Sean's solution worked. Thank you very much![/quote]You are quite welcome. Glad that worked for you.Wed, 27 Feb 2013 07:56:18 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423816-1292-1.aspxSean's solution worked. Thank you very much!Tue, 26 Feb 2013 08:05:14 GMT5280_Lifestylehttp://www.sqlservercentral.com/Forums/Topic1423816-1292-1.aspx[quote][b]BriPan (2/26/2013)[/b][hr]but If performance is in concern then go for[code="sql"]select CandidateID, max(YourDateColumn)from YourTablegroup by CandidateID [/code][/quote]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.Tue, 26 Feb 2013 03:48:47 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1423816-1292-1.aspxbut If performance is in concern then go for[code="sql"]select CandidateID, max(YourDateColumn)from YourTablegroup by CandidateID [/code]Tue, 26 Feb 2013 03:39:54 GMTBriPanhttp://www.sqlservercentral.com/Forums/Topic1423816-1292-1.aspxYou can also use ROW_NUMBER()[code="sql"]SELECT *FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY CandidateID ORDER BY YourDateColumn DESC ) AS RN, * FROM YourTable ) AS YTWHERE YT.RN = 1[/code]Tue, 26 Feb 2013 00:50:44 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1423816-1292-1.aspx[quote][b]5280_Lifestyle (2/25/2013)[/b][hr]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.[/quote]Like this?select CandidateID, max(YourDateColumn)from YourTablegroup by CandidateIDMon, 25 Feb 2013 18:00:05 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423816-1292-1.aspxI'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:[code="plain"]200843 2012-02-14 07:49:00200843 2012-02-15 06:56:00231022 2012-02-14 07:49:00233525 2012-01-30 21:15:00276699 2012-01-30 21:14:00291019 2012-01-30 21:15:00298805 2012-01-30 21:15:00298805 2012-02-15 06:56:00298805 2012-02-21 09:39:00306011 2012-02-14 07:49:00306011 2012-02-15 06:56:00306011 2012-02-21 09:55:00313538 2012-01-30 21:15:00316135 2012-01-30 21:14:00[/code]I need the query to return the following...[code="plain"]200843 2012-02-15 06:56:00231022 2012-02-14 07:49:00233525 2012-01-30 21:15:00276699 2012-01-30 21:14:00291019 2012-01-30 21:15:00298805 2012-02-21 09:39:00306011 2012-02-21 09:55:00313538 2012-01-30 21:15:00316135 2012-01-30 21:14:00[/code]Mon, 25 Feb 2013 16:31:32 GMT5280_Lifestyle