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.

Advertisements
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s