SQLServer SQL query with a row counter

Question

I have a SQL query, that returns a set of rows:

SELECT id, name FROM users where group = 2

I need to also include a column that has an incrementing integer value, so the first row needs to have a 1 in the counter column, the second a 2, the third a 3 etc

The query shown here is just a simplified example, in reality the query could be arbitrarily complex, with several joins and nested queries.

I know this could be achieved using a temporary table with an autonumber field, but is there a way of doing it within the query itself ?

Answer 1

In SQL Server 2005 and up, you can use the ROW_NUMBER() function, which has options for the sort order and the groups over which the counts are done (and reset).

Answer 2

For starters, something along the lines of:

SELECT my_first_column, my_second_column,
    ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table

However, it's important to note that the ROW_NUMBER() OVER (ORDER BY ...) construct only determines the values of Row_Counter, it doesn't guarantee the ordering of the results.

Unless the SELECT itself has an explicit ORDER BY clause, the results could be returned in any order, dependent on how SQL Server decides to optimise the query. (See this article for more info.)

The only way to guarantee that the results will always be returned in Row_Counter order is to apply exactly the same ordering to both the SELECT and the ROW_NUMBER():

SELECT my_first_column, my_second_column,
    ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table
ORDER BY my_order_column  -- exact copy of the ordering used for Row_Counter

The above pattern will always return results in the correct order and works well for simple queries, but what about an "arbitrarily complex" query with perhaps dozens of expressions in the ORDER BY clause? In those situations I prefer something like this instead:

SELECT t.*
FROM
(
    SELECT my_first_column, my_second_column,
        ROW_NUMBER() OVER (ORDER BY ...) AS Row_Counter  -- complex ordering
    FROM my_table
) AS t
ORDER BY t.Row_Counter

Using a nested query means that there's no need to duplicate the complicated ORDER BY clause, which means less clutter and easier maintenance. The outer ORDER BY t.Row_Counter also makes the intent of the query much clearer to your fellow developers.