Every once in a while there is a need to pull the rows with a row number. Here are a few solutions that I have seen to work well.
Below is a sample table with employees that has employee name and employee address columns:
CREATE TABLE Employees (
employee_name VARCHAR(50) PRIMARY KEY,
employee_address VARCHAR(100));
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Blake Anderson', '2048 River View Rd.');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Ana Williams', '9055 East Blvd.');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Robert Schmidt', '3400 Windsor Street');
INSERT INTO Employees (employee_name, employee_address)
VALUES ('Sarah Reese', '1045 Coral Rd.');
SQL Server 2000 and SQL Server 2005
Using an IDENTITY column and a temporary table
This solution is based on creating a temporary table with IDENTITY column used to provide a row number. This approach provides very good performance. Here are the steps:
-- Create the temp table
CREATE TABLE #EmployeeRowNumber (
rn INT IDENTITY (1, 1),
employee_name VARCHAR(50),
employee_address VARCHAR(100));
-- Generate the row number
-- To achieve an ordered list the names are sorted
INSERT #EmployeeRowNumber (employee_name, employee_address)
SELECT employee_name, employee_address
FROM Employees
ORDER BY employee_name;
-- Select the row number
SELECT employee_name, employee_address, rn
FROM #EmployeeRowNumber
ORDER BY rn;
-- Results
employee_name employee_address rn
---------------- ------------------------ -----------
Ana Williams 9055 East Blvd. 1
Blake Anderson 2048 River View Rd. 2
Robert Schmidt 3400 Windsor Street 3
Sarah Reese 1045 Coral Rd. 4
Using a subquery to count the number of rows
This solution is based on using a subquery on a unique column (or combination of columns) to count the number of rows. Here is how it looks with the sample data provided above:
SELECT employee_name, employee_address,
(SELECT COUNT(*)
FROM Employees AS E2
WHERE E2.employee_name <= E1.employee_name) AS rn
FROM Employees AS E1
ORDER BY employee_name;
If the values in the column are not unique then duplicate row numbers will be generated. That can be resolved by adding a tiebreaker column that will guarantee the uniqueness. This approach is a slower method than using an IDENTITY column and a temporary table. Since it will incur (n + n2) /2 row scans it may not be practical to use on a large table.
SQL Server 2005
Using the ROW_NUMBER function
In SQL Server 2005 the new function ROW_NUMBER provides the fastest approach to solve the problem:
SELECT employee_name, employee_address,
ROW_NUMBER() OVER(ORDER BY employee_name) AS rn
FROM Employees
ORDER BY employee_name;
Additional resources:
How to dynamically number rows in a SELECT Transact-SQL statement: http://support.microsoft.com/kb/186133
SQL Server 2005 Ranking Functions: http://msdn2.microsoft.com/en-us/library/ms189798.aspx
Book: “Inside Microsoft SQL Server 2005: T-SQL Querying” by Itzik Ben-Gan, Lubor Kollar and Dejan Sarka
