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”.
CI_ErrorTypes


ErrorType

ErrorTypeName

1

InfrastructuralError

2

DiscoveryError

3

EnforcementError

4

ConflictError

5

LaunchError

1024

AppCycleDependence

2048

AppCycleSuperSedent

4096

DCMCycleReference

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
ã€€
SELECT ErrorType
,ErrorTypeName
,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 
,ErrorTypeName
,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 
SELECT ErrorType
,ErrorTypeName
,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 
,ErrorTypeName
,CASE WHEN LEN(ErrorTypeName) < 15 THEN 1 ELSE 2 END AS [PartitionByCreated]
,ROW_NUMBER() OVER(PARTITION BY CASE WHEN LEN(ErrorTypeName) < 15 THEN 1 ELSE 2 END ORDER BY ErrorType) AS [New_Row_Number]
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 