This function is extremely useful. As of late, I use it mainly to help me manage a loop within a loop, which we will save for another time because I need to release said recent work to other users who are not me. However, I want to document this information somewhere for quick and easy reference of my own.
Here is a scenario. I want to create a temporary table of records, and I need to iterate through each row, and I need to a do it in a specific order. I need the row number to act like a record number for just this temporary table, like so:
Declare @tblExample table (idRecord int, idLocation int, dtDate datetime)
Insert into @tblExample
Select Row_Number() Over (order by dtDate) as idRecord, idLocation, dtDate
From tblTask a
join tblLocation b
on a.idLocation = b.idLocation
Where 1 = 1
and dtDate >= getdate()
Sometimes I end up removing items from a temporary table and need to reset the idRecord column so that it updates to new row numbers. I can do that with the following code:
Here is how to do that:
Update a
Set idRecord = a.idRecordNew
From
(
Select Row_Number() Over (Order by idRecord, idLocation, dtDate) as idRecordNew
From @tblExample
) a
That is all for this post.