SQL Tip: Creating Row Numbers

ConfigMgr Admins have asked about this before and I once wrote an internal post on this...here it is for anyone to see.

Sometimes when writing a query it is helpful (or necessary) to have row numbers. In this SQL tip we’ll learn about “ROW_NUMBER”.

Not surprisingly, ROW_NUMBER allows you to create a row number for each record returned in your query. However, it does require you to define how the rows are numbered. Let’s dig into the examples.
I’ll be using the "CI_ErrorTypes" table in the ConfigMgr database. I know, I know, I should only use views, but I don't think there is a view for this table and it has a small number of records which will be perfect for examples. 🙂
The example table:
Parts of the ROW_NUMBER function:        
In order to use the ROW_NUMBER function the keyword “OVER” must be included along with an “ORDER BY” clause. A “PARTITION BY” clause can be included but is not required.
The ORDER BY in the ROW_NUMBER function determines how the row number will be assigned to the result set. It is important to note that this ORDER BY is not the same as the regular ORDER BY used toward the end of the query. I’ll prove this point in the examples.
The PARTITION BY clause allows you to define whether you want to restart the numbering based on a group. For example, with only an ORDER BY clause you can create a unique row number for the entire result set; with a PARTITION BY clause you can create a unique row number for each GROUP in the result set.

SELECT ROW_NUMBER() -- The function
                    OVER( -- the OVER clause
PARTITION BY [Column] -- the partition by clause (optional)
ORDER BY [Column] -- the order by clause
) AS [RowNumber] -- the column name/alias


The basic ROW_NUMBER:
The majority of the time when a row number is desired a “basic” row number is all that is required. I call this a “basic row number” because it simply creates a row number based on one column of the result set and does not require a PARTITION BY clause. The first example will use the “ErrorType” column to create a row number. An “ASC” or “DESC” (sort ASCending or DESCinding) can be added in the ORDER BY clause to determine how the row number is assigned but if not added ASCending is the default.

SELECT ErrorType
,ROW_NUMBER() OVER(ORDER BY ErrorType) AS [New_Row_Number]
FROM dbo.CI_ErrorTypes;

ErrorType ErrorTypeName New_Row_Number
1 InfrastructuralError 1
2 DiscoveryError 2
3 EnforcementError 3
4 ConflictError 4
5 LaunchError 5
1024 AppCycleDependence 6
2048 AppCycleSuperSedent 7
4096 DCMCycleReference 8
We can also create the row number based on the ErrorTypeName column instead of the ErrorType column:
SELECT ErrorType
,ROW_NUMBER() OVER(ORDER BY ErrorTypeName) AS [New_Row_Number]
FROM dbo.CI_ErrorTypes;

ErrorType ErrorTypeName New_Row_Number
1024 AppCycleDependence 1
2048 AppCycleSuperSedent 2
4 ConflictError 3
4096 DCMCycleReference 4
2 DiscoveryError 5
3 EnforcementError 6
1 InfrastructuralError 7
5 LaunchError 8
The ORDER BY at the end of a query vs an ORDER BY clause in the “OVER” clause:
Now to prove the point that the ORDER BY in the ROW_NUMBER is not the same as the ORDER BY at the end of the query, we’ll use an ORDER BY at the end of the query and change the ordering in the ROW_NUMBER function.

SELECT ErrorType
,ROW_NUMBER() OVER(ORDER BY ErrorType ASC) AS [New_Row_Number_Ascending]
,ROW_NUMBER() OVER(ORDER BY ErrorType DESC) AS [New_Row_Number_Descending]
FROM dbo.CI_ErrorTypes
ORDER BY ErrorType;


ErrorType ErrorTypeName New_Row_Number_Ascending New_Row_Number_Descending
1 InfrastructuralError 1 8
2 DiscoveryError 2 7
3 EnforcementError 3 6
4 ConflictError 4 5
5 LaunchError 5 4
1024 AppCycleDependence 6 3
2048 AppCycleSuperSedent 7 2
4096 DCMCycleReference 8 1
A slightly more complicated ROW_NUMBER (using the PARTITION BY):
In order to use a PARTITION BY I’ll need to create a grouping. So, I’ll create two groups based on the length of the values in the “ErrorTypeName” column. I’ll add this value to the result set to help illustrate how the PARTITION BY works. You’ll see that the “PartitionByCreated” column has two groups; three records in group “one” and five records in group “two”. Each group will have its own “row number” (because we PARTITIONed the row numbers BY our made up group). 
SELECT ErrorType
,CASE WHEN LEN(ErrorTypeName) < 15 THEN 1 ELSE 2 END AS [PartitionByCreated]
FROM dbo.CI_ErrorTypes;

ErrorType ErrorTypeName PartitionByCreated New_Row_Number
2 DiscoveryError 1 1
4 ConflictError 1 2
5 LaunchError 1 3
1 InfrastructuralError 2 1
3 EnforcementError 2 2
1024 AppCycleDependence 2 3
2048 AppCycleSuperSedent 2 4
4096 DCMCycleReference 2 5


Multiple columns can be used in the PARTITION BY and/or the ORDER BY clauses. In fact, this may be necessary or desired based on the result set you have to work with.
This function has come in very handy over the years for various reasons. Now you have an idea of how to create a row number if you ever need to!
Comments (0)

Skip to main content