Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER.
Lets understand this by following example
In this example we have person table which have firstname, last name, middlename columns. Now we want to fetch records with a extra column ROW_Number() so we write following query
SELECT ROW_NUMBER() OVER (ORDER BY LastName) As ROWNO,FirstName,MiddleName,LastName FROM [Person].[Person]
When we run above query we get following result set. If you see below result set we have one extra column which is ROWNO
Lets understand the query in this we are getting sequence or Row number order by Last name.
There is one more option with ROW_NUMBER which is partition by
lets understand this by below query what we want for we want row number for each record based on last name means whenever new last name introduce row number start again from one. to achieve this we have to write following query
SELECT ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY LastName) As rowNo,FirstName,MiddleName,LastName FROM [Person].[Person]
See below snap when we run we get following result
If you see above snap you will find that when the Last name change row number also change so we did partition by last name means for each change.
I hope you might have used earlier but I thought It might be possible it will helpful to someone who not aware.
Enjoy!!
RJ
