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.');
-- 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
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;
SELECT employee_name, employee_address,
ROW_NUMBER() OVER(ORDER BY employee_name) AS rn
FROM Employees
ORDER BY employee_name;
Labels: row number, sql server, t-sql programming