Occasionally its useful to return row numbers when executing a select statement in SQL so that each row of the returned result set has a unique id similar to how an identity column would work on a table and we are able to achieve this by using the
ROW_NUMBER windowing function.
If your new to windowing functions then you can find more detailed information on the MSDN website at the following link https://msdn.microsoft.com/en-us/library/ms189461.aspx
As an example below we have a simple select statement that returns product number and product name from the adventure works product table.
SELECT ProductNumber , Name FROM production.product
|BE-2349||BB Ball Bearing|
|BE-2908||Headset Ball Bearings|
Now by adding the
ROW_NUMBER windowing function into our select statement we are able to include a sequential numbered identifier in the result set.
SELECT ProductNumber , Name , ROW_NUMBER() OVER(ORDER BY ProductNumber) AS RowNumber FROM production.product
|BE-2349||BB Ball Bearing||3|
|BE-2908||Headset Ball Bearings||4|
In the above example we added the windowing function
ROW_NUMBER() OVER(ORDER BY ProductNumber) to our select statement, the first part
ROW_NUMBER() is the tsql function that is going to return the sequential number, the next part contains the
OVER clause and this is windowing function part and without it the ROW_NUMBER function would not work for us within the select statement. The windowing part of the statement
OVER(ORDER BY ProductNumber) in very simple temrs tells SQL server what records in the result set we want to apply the function to and in what order the function should be applied.
In the above example we are returning the result set and applying the ROW_NUMBER function to all the records returned so that the first record starts at number one and the next is n+1 etc… which for returning the row number is normally what you would want to do. But we are also able to use the
PARTITION part of the over clause to tell the function to restart the number sequence based on certain criteria.
So for example if we wanted to restart the number sequence each time the first two characters of the product number changed we could alter the above select statement and add in a partition clause into the over section of our statement to do just that.
SELECT ProductNumber , Name , ROW_NUMBER() OVER(PARTITION BY LEFT(ProductNumber,2) ORDER BY ProductNumber) AS RowNumber FROM production.product
Now that we have added in a
PARTITION clause and the
LEFT() function the outcome is that the row number will be reset back to one each time the first two characters change and our result set would now look like the below.
|BE-2349||BB Ball Bearing||1|
|BE-2908||Headset Ball Bearings||2|
As you can see the first three records all have the row number 1 as they all have different characters at the start where as the fourth record has a row number of 2 as it is the second record in the ‘BE’ sequence.