ROW_NUMBER is a function built-in to SQL Server that will return a row number for each record in your result set. You can further change resulting row number to reset the row number based on some value in the result set. I will show you examples of both below.
ROW_NUMBER Basics
To show the row number in SQL Server, you need to use the ROW_NUMBER function. This function is broken down in to two parts.
- PARTITION BY – If you supply this parameter, then the row number will reset based on the value changing in the columns supplied. This is kinda like using a GROUP BY.
- ORDER BY – This is the specified order of the row number that you would like. If you wanted the order of the row number to increment by an employee name (alphabetically), you do that here.
With this function I think examples will help paint the full picture.
In the following query, the results will show a row number with each record. The number will start at 1 and increment for every record in order of AnimalName. You can see that that the order of the row number increment is specified as a parameter in to the ROW_NUMBER() function.
SELECT AnimalID, AnimalName, AnimalType, ROW_NUMBER() OVER(ORDER BY AnimalName) AS RowNumber FROM Animal
[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,RowNumber
166,Alpaca,Mammal,1
168,Camel,Mammal,2
162,Carabao,Mammal,3
171,Cat,Mammal,4
163,Cattle,Mammal,5
184,Chicken,Bird,6
182,Deer,Mammal,7
185,Duck,Bird,8
186,Goose,Bird,9
189,Pigeon,Bird,10
188,Quail,Bird,11
187,Turkey,Bird,12
[/table]
In the above example, the row number never resets. It started at 1 and kept going through all the records. But what if you wanted to reset the row number back to 1 based on a value changing in your result set. In the following example, every time that the AnimalType changes we will reset the row number back to 1. This way, each AnimalType would have it’s own set of row numbers. We accomplish this by listing the columns that we want to group the row numbers by in the PARTITION BY parameter.
SELECT AnimalID, AnimalName, AnimalType, ROW_NUMBER() OVER(PARTITION BY AnimalType ORDER BY AnimalName) AS RowNumber FROM Animal A ORDER BY AnimalType, AnimalName
[table width=”500″ colwidth=”20|100|50″ colalign=”left|left|center|left|right”]
AnimalID,AnimalName,AnimalType,RowNumber
184,Chicken,Bird[attr style=”background-color:#F2F5A9″],1[attr style=”background-color:#F2F5A9″]
185,Duck,Bird,2
186,Goose,Bird,3
189,Pigeon,Bird,4
188,Quail,Bird,5
187,Turkey,Bird,6
166,Alpaca,Mammal[attr style=”background-color:#F2F5A9″],1[attr style=”background-color:#F2F5A9″]
168,Camel,Mammal,2
162,Carabao,Mammal,3
171,Cat,Mammal,4
163,Cattle,Mammal,5
182,Deer,Mammal,6
[/table]
Good Things To Know
You can specify multiple columns in the PARTITION BY and ORDER BY parameters by separating them with a comma.
You can specify ASC or DESC in the ORDER BY parameter if you like as well.
Reference: http://msdn.microsoft.com/en-us/library/ms186734.aspx