Generating Row Numbers in SQL

Use of the ROW_NUMBER() function to generate sequential numbers

This is one that I use regularly, and forget everytime what the syntax is.

It is really useful to be able to list rows of data for SQL Server with a sequential number on each row, which can be done via the ROW_NUMBER() function...

SELECT [ValCol], ROW_NUMBER() OVER(ORDER BY [OrderCol]) AS [RowNum]
FROM [MyTable]

This could result in...

ValCol  RowNum
First   1
Second  2
Third   3

There is also the ability to split/partition the sequentail numbers based on specific columns...

SELECT [ValCol], [SplitCol],
  ROW_NUMBER() OVER(PARTITION BY [SplitCol] ORDER BY [OrderCol]) AS [RowNum]
FROM [MyTable]

The sequence numbering now restarts for each unique [SplitCol] value...

ValCol  SplitCol  RowNum
First   One       1
Second  One       2
Third   Two       1