How to add sequential row numbers to a table using a SQL CTE

Colin Maitland, 23 February 2023

In this article, I will demonstrate how to add a unique sequential row number to records in a SQL Server table using a SQL Common Table Expression (CTE).

This is an example of selected columns from a table named Opportunity. This list of Opportunities selected here has been sorted by the Created On date and Name of the Opportunity. Notice that the RowNumber column does not contain any row numbers.

What I want to do is to populate the RowNumber column with a unique sequential row number sorted by the Created On date and Name of the Opportunity.

This is easily done using SQL.

STEP 1

The first step is to write a SQL statement that generates a row number for each row sorted by the Created On date and Name of the Opportunity. Here the column containing the generated row number is named RowID rather than RowNumber. The RowID column is a temporary column that only exists in the result set.

The ROW_NUMBER() function generates the row number for each row and the ORDER BY clause is used within the OVER() function to specify the order of the rows for which the row numbers are to be generated.

STEP 2

The second step is to convert the SQL statement to a Common Table Expression (CTE) along with adding a corresponding UPDATE statement. This is done by adding these WITH … AS and UPDATE … SET clauses as shown here.

This SQL statement can be simplified by removing from the SELECT statement any columns not referenced by the UPDATE statement.

The SQL statement converts the Opportunities returned by the SELECT statement into a temporary result set known as a Common Table Expression (CTE). In this example have named the result set CTE. However, you can use other valid names such as OpportunityRowNumbers instead of CTE.

The UPDATE statement is executed for each row in the CTE and updates the RowNumber from the RowID.

CONCLUSION

It’s as easy as that! As a result, the RowNumber column in the Opportunity table now contains the unique sequential row number for each record.

A SQL statement such as the following may be used to select the Opportunities sorted by the RowNumber instead of by the Created On date and Name.

Common Table Expressions (CTE) are a very powerful type of expression to use when working with data using SQL. I use CTEs all the time for numbering records in SQL tables and for generating unique sequential row numbers that are specific groups of records.

In this final example, I have generated a unique sequential row number for each group of Opportunities based on the Name. The PARTITION BY clause and the TRIM() and CHARINDEX() functions are used to group the Opportunities by the part of the Name that precedes the first occurrence of ‘ (‘, i.e. ‘North Island Quantum Ltd’ verses ‘South Island Quantum Ltd’.

Finally, you can access the detailed Microsoft technical documentation on working with CTEs here: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16.