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.