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
精彩评论