|
|
Posted by Plamen Ratchev on 05/14/07 17:36
As Alex suggested, you can use the ROW_NUMBER function in SQL Server 2005.
Here is one solution that does that:
WITH Registrations AS
(
SELECT RegistrationID,
StatusID,
StatusTimeStamp,
ROW_NUMBER() OVER(
PARTITION BY RegistrationID
ORDER BY StatusTimeStamp DESC,
StatusID) AS rn
FROM #RegStatus
WHERE RegistrationID = COALESCE(@RegID, RegistrationID)
)
SELECT R.RegistrationID,
S.StatusID,
R.StatusTimeStamp,
S.StatusName,
S.StatusCategoryID
FROM #Status AS S
INNER JOIN Registrations AS R
ON S.StatusID = R.StatusID
WHERE StatusCategoryID = @RegStatusCatID
AND rn = 1;
A few notes:
- If you decide to use this approach, the ROW_NUMBER function can benefit
from index created on the partitioning column and the sort columns. In this
case would be (RegistrationID, StatusTimeStamp, StatusID).
- The way you created your example, it generates duplicate StatusTimeStamp
dates for a registrant. For that reason I added StatusID in the ORDER BY
clause of ROW_NUMBER to return deterministic result. However, the result may
or may not match your original solution. That is because you had the
IDENTITY column which was an indicator of the latest inserted row. Here I
used the StatusTimeStamp as an indicator for the latest status. If you
expect to have duplicate dates and the StatusID is not good for a
tiebreaker, then I guess using the IDENTITY would be the only solution.
- I assumed the goal is to avoid using the IDENTITY column. If not, then
using ROW_NUMBER with ORDER BY UniqueRowID DESC will provide deterministic
result.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|