Selecting 2 Most Recent Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting 2 Most Recent Rows

Author

Topic

jpheep
Starting Member

1 Post

Posted - 2011-05-17 : 14:15:29

Hi,

I've been trying a while but couldn't find a solution that works for me. I have a single table that has columns for Interview_Date, Candidate_ID, Position_ID, and Interview_ID.

What I am trying to do is select the two most recent records for each unique combination of Candidate_ID and Position_ID.

So if my table has the following:
Candidate_ID, Position_ID, Interview_ID, Interview_Date
Can001, Pos001, Int001, 1/1/2011
Can001, Pos001, Int002, 1/2/2011
Can001, Pos001, Int003, 1/3/2011
Can001, Pos002, Int004, 1/1/2011
Can001, Pos002, Int005, 1/4/2011
Can001, Pos002, Int006, 1/5/2011
Can002, Pos003, Int007, 1/1/2011
Can002, Pos003, Int008, 1/2/2011
Can002, Pos003, Int009, 1/3/2011
Can002, Pos002, Int010, 1/1/2011
Can002, Pos002, Int011, 1/4/2011
Can002, Pos002, Int012, 1/5/2011

I want the result to be:
Can001, Pos001, Int002, 1/2/2011
Can001, Pos001, Int003, 1/3/2011
Can001, Pos002, Int005, 1/4/2011
Can001, Pos002, Int006, 1/5/2011
Can002, Pos003, Int008, 1/2/2011
Can002, Pos003, Int009, 1/3/2011
Can002, Pos002, Int011, 1/4/2011
Can002, Pos002, Int012, 1/5/2011

I have tried the following without much luck:

Select S.position_id, S.candidate_id, S.interview_date

From interviews S

Where (Select Count(*) From interviews Where Interview_Id = S.interview_id And interview_date > S.interview_date) < 2

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-17 : 14:17:33

select a.*
from
(select Candidate_ID, Position_ID, Interview_ID, Interview_Date
,[row] = row_number() over(partition by Candidate_ID, Position_ID order by Interview_Date desc)
) a

where a.row < 3

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-30 : 07:38:09

More methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources