Select all rows which consists of 2 latest rows for each distinct field in a column in SQL

Question

I have a table named tblHumanResources in which I want to get the collection of rows which consists of the latest 2 rows per distinct empID. (Latest in terms of effectiveDate).

Scenario: I want the collection of entries to be ordered by empID, and then get 2 rows per empID which has the latest effectiveDate (top 2 of effectiveDate if ordered in a descending manner).

Note: I am using SQL Server 2008 and my table has thousands of rows.

tblHumanResources Table

| empID | effectiveDate |  Company | Description
| 0-123 |    1999-04-18 | DFD Comp | Analyst
| 0-234 |    2007-04-22 | ABC Comp | Manager
| 0-222 |    2012-02-19 | CDC Comp | Janitor
| 0-213 |    2009-05-03 | CBB Comp | Teller
| 0-223 |    2012-01-23 | CBB Comp | Teller
| 0-223 |    1999-05-27 | CBB Comp | Teller
| 0-123 |    2014-01-25 | DFD Comp | Analyst
| 0-234 |    1999-01-23 | ABC Comp | Manager
| 0-222 |    2014-12-13 | CDC Comp | Janitor
| 0-213 |    2014-02-12 | CBB Comp | Teller
| 0-223 |    2005-03-26 | CBB Comp | Teller
| 0-123 |    2005-02-05 | DFD Comp | Analyst
| 0-234 |    2014-05-18 | ABC Comp | Manager
| 0-222 |    2001-06-23 | CDC Comp | Janitor
| 0-213 |    2003-05-12 | CBB Comp | Teller

//and so on...

I want to display something like this:

| empID | effectiveDate |  Company | Description
| 0-123 |    2014-01-25 | DFD Comp | Analyst
| 0-123 |    2005-02-05 | DFD Comp | Analyst
| 0-213 |    2014-02-12 | CBB Comp | Teller
| 0-213 |    2009-05-03 | CBB Comp | Teller
| 0-222 |    2014-12-13 | CDC Comp | Janitor
| 0-222 |    2012-02-19 | CDC Comp | Janitor
| 0-223 |    2012-01-23 | CBB Comp | Teller
| 0-223 |    2005-03-26 | CBB Comp | Teller
| 0-234 |    2014-05-18 | ABC Comp | Manager
| 0-234 |    2007-04-22 | ABC Comp | Manager

//and so on...

Any help would be much appreciated.

PS: I wouldn't be able to try your codes right now for my dev laptop isn't with me. I'd be trying your answers after a few hours to be able to properly choose the answer that solves the problem.

Thank you.

M.Ali · Accepted Answer · 2014-06-10T00:26:08.690

2

Using Sub-Query

SELECT empID , effectiveDate ,  Company , [Description] 
FROM 
  (
    SELECT empID , effectiveDate ,  Company , [Description]
      , ROW_NUMBER() OVER (PARTITION BY empID ORDER BY effectiveDate DESC) rn
    FROM TableName
   ) A 
WHERE rn <= 2

Using CTE

;WITH CTE 
AS
  (
    SELECT empID , effectiveDate ,  Company , [Description]
      , ROW_NUMBER() OVER (PARTITION BY empID ORDER BY effectiveDate DESC) rn
    FROM TableName
  ) 
SELECT  empID , effectiveDate ,  Company , [Description] 
FROM CTE
WHERE rn <= 2

Without Using Ranking Functions

SELECT t1.empID 
      ,t1.effectiveDate 
      ,t1.Company 
      ,t1.[Description]
FROM TableName t1
WHERE t1.effectiveDate IN (SELECT TOP 2 effectiveDate 
                           FROM TableName 
                           WHERE empID = t1.empID
                           ORDER BY effectiveDate DESC)

edited Jun 10 '14 at 00:26

answered Jun 09 '14 at 16:04

M.Ali

67,945
13
101
127

My SQL Server does not support Row_Number() function due to unknown reason. Do we have other ways without using the function? My apologies for the inconvenience. @M.Ali – Sai Jun 09 '14 at 16:05
1

@Sai that would mean you are running on Sql prior to 2005, e.g. 2000? – StuartLC Jun 09 '14 at 16:07
2

If you have sql server 2008 it must support Ranking functions. – M.Ali Jun 09 '14 at 16:08
Execute this `SELECT @@VERSION` and see what version you get back. – M.Ali Jun 09 '14 at 16:09
It gave me an error when I tried Row_Number() function awhile ago. I'd give this a try after a few hours. I'll keep you posted. Thank you. @M.Ali – Sai Jun 09 '14 at 16:09
Are there any other way besides using a function? It gives me an error that this function is not supported. @M.Ali – Sai Jun 10 '14 at 00:15
Post your attempt in your question lets see what you have got there? – M.Ali Jun 10 '14 at 00:17
@Sai have a look now I have updated my answer with a solution without using ranking function. but I still think you need to check your query as whether you have not written it correctly or you are on a version older than 2005, anyway if it helps please accept the answer. Thank you. – M.Ali Jun 10 '14 at 00:28
Okay let me try it. Just a question though, do I have to choose all the columns one by one (empID , effectiveDate and so on..)? Because my actual table has more than 15 columns more or less. Can I use *? @M.Ali – Sai Jun 10 '14 at 00:31
I tried the * and it worked but I added order by empID at the end of the snippet code. Thank you! @M.Ali – Sai Jun 10 '14 at 00:48

Select all rows which consists of 2 latest rows for each distinct field in a column in SQL

1 Answers1