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