Row_Number SQL Server

Row_Number or Sequence Generator in SQL Server

Is is used to generate a sequence number or a row number in a result table. That is if you want to run a query and you rank certain records in certain order, this where you can use Row Number function. It is a function and is follow be ()

Usage

select ID, row_number() over(order by id) as RowNumber from mytable

will create the sequence number in ORDER based on id. This will be the simplest squence. When ever ID changes, row number is incremented. Since ID is our primary key, it changes for every record, the RowNumber will be same as ID (if not record was deleted).

Lets take it one more level. Lets say your table has data arranged by states, that is Connecticut (CT), Massachusetts (MA). You want to rank your records differently in each state. You can do that using PARTITION BY clause

select ID, row_number() over(PARTITION BY STATE order by id) as RowNumber from mytable

This will start giving sequence numbers to each record based on each state. So if you have 10 records in MA and 5 in CT. The row number will be 1-10 for MA and then 1-5 for CT.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*