开发者

Need some help with a SQL Query

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   开发者_StackOverflow          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.


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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜