Posted by Mark on 05/14/07 19:34

Alex and Plamen, thanks so much for the help.

I'm using a UDF to return this data, so I've opted out of using the
Common Table Expression. The Row_Number helped tons. Over the test
data (not much) I've improved efficiency quite a bit, according to the
execution plans. I'm going to run this on live data soon, to see how
it works. I've changed my query to be as follows;

SELECT *
FROM #Status S
JOIN
(
SELECT RegistrationID,
StatusID,
StatusTimeStamp,
[UniqueRowID],
ROW_NUMBER()
OVER(
PARTITION BY RegistrationID
ORDER BY [UniqueRowID] DESC
) AS rn
FROM #RegStatus RS
where (
@RegID is null
or (@RegID is not null
and RS.RegistrationID = @RegID)
)
) RS
ON S.StatusID = RS.StatusID
AND RS.RN = 1
AND S.StatusCategoryID = @RegStatusCatID

Thanks Again!

Mark

• Next in forum: Re: TRIGGER after UPDATE
• Prev in forum: Re: UNION SELECT from Multiple Subselects
• Thread view: Re: Want to make ''Get Latest Status'' function more efficient

[Reply to this message]