Need some help with a SQL Query

Question

Hi all,

I need some help with a SQL query for SQL Server 2005. Here is an example of the data in the table to be queried:

Id    PersonId      PayrollNum    ContractId     PayrollFrom     PayrollTo
---------------------------------------------------------------------------
 1     432642         85110892     1             01/05/2009      31/05/2009
 2     432642         85110892     1             01/06/2009      30/06/2009
 3     432642         85110892     1             01/07/2009      31/07/2009
 4     432642         85110892     2             01/05/2009      31/05/2009
 5     432642         85110892     2             01/06/2009      30/06/2009
 6     432642         85110892     2             01/07/2009      31/07/2009
 7     432642         85110892     2             01/08/2009      31/08/2009
 8     432642         35110892     1             01/06/2009      30/06/2009
 9     432642         35110892     1             01/05/2009      31/05/2009
10     432642         35110892     1             01/07/2009      31/07/2009

(I hope that gets formatted ok - it's hard doing tables with this thing!)

The output I need is as follows:

Id
--
 1
 4
 9

An explanation: What I basically need are the unique Id values for the records that differ by PersonId, PayrollNum and ContractId and also be the lowest PayrollFrom date for each grouping. I'm not sure how to better explain it? Hopefully you can look at the output to help understand the query's requirements.

Let me know if I need to explain this better (or if you think you understand it and can explain it better please do so).

Thanks for your help, James.

Answer 1

WITH CTE AS (
SELECT
    /* uncomment if needed PersonId, PayrollNum, ContractId, PayrollFrom, */
    ID,
    ROW_NUMBER() OVER (
        PARTITION BY PersonId, PayrollNum, ContractId
        ORDER BY PayrollFrom ASC -- thank you to Peter Lang DESC
     ) AS Ranking
FROM
    MyTable
)
SELECT
    ID
FROM
    CTE
WHERE
    Ranking = 1

Edit, after comment

Try something like ISNULL(PersonId, -ID) instead of PersonId to force it unique to differentiate each NULL. I use -ID to it shouldn't clash where ID = a valid PersonID

Answer 2

You will need a query that looks something like this

SELECT  *
FROM    myTable t INNER JOIN
     (
      SELECT PersonId, 
        PayrollNum,
        ContractId ,
        MIN(PayrollFrom) MIN_PayrollFrom
      FROM MyTable
      GROUP BY PersonId, 
        PayrollNum,
        ContractId
     ) minDates ON t.PersonId = minDates.PersonId
        AND  t.PayrollNum = minDates.PayrollNum
        AND  t.ContractId = minDates.ContractId
        AND  t.PayrollFrom = minDates.MIN_PayrollFrom