I am asking the question as I begin the research on how to do this because I am not sure if I will figure this out on my own. I have not used the Row_Number() function that often, and most of those times it was after an old mentor of mine had written it for me.
Here's what I am trying to achieve. I am re-working a nested derived table:
From dbo.rvlPropertyStatus Inner Join
(Select Max(PropertyStatusDate) As maxDate
Group By rvlPropertyID) As maxStatus
On rvlPropertyStatus.PropertyStatusDate = maxStatus.maxDate
And rvlPropertyStatus.rvlPropertyID = maxStatus.rvlPropertyID
In my system the joined derived table return 17,635 records, but the entire join returns 17,637 records. I believe this is being caused by 2 instances where the same property ID has two instances of the same date.
My thought is to use Row_Number() to accomplish this. I believe I know how to do this, but that is still in the conceptual phase.
My question is in two parts:
1. - The obvious question of how. I will be working on that as soon as I post this, but I don't have an exceeding level of confidence in my skills with this function yet.
2. - Is there a better means of achieving my goal? In other words what are the places where this is going to turn around and bite me that I haven't thought of yet?
“Any fool can know. The point is to understand.”
- Albert Einstein
- Homer Simpson