Returning a Row Number in a Query

By Bill Graziano on 5 December 2000 | Tags: Queries


Oleg writes "Hello guys. How can I receive resultset's row number as a column? . . . Thanks in advance." We get this question quite a bit and I always answer "use a temp table". Well here are the details on exactly how to do that.

The full text of his question is "Hello guys. How can I receive resultset's row number as a column?

There is an example.
Table TABLE has the only numeric field FIELD.
I need:

select XXX num, FIELD from TABLE
result:
num	field
--- ------
1 2345
2 8475
3 5347
4 3464
So the question is. What should I replace XXX with?
SQL Server 7.0.

Thanks in advance."




I'll use the employee table in the pubs database for this example. Let's say you want all the employees with job_id = 10 and you want them sorted by last name and you want each row numbered. The statment to return this information (without the row number) is:

SELECT emp_id, lname, fname, job_id
FROM employee
WHERE job_id = 10
ORDER BY lname


This returns the information but doesn't give you a row number. The emp_id field is the primary key for this table. The first step is to build a temporary table with an IDENTITY column and our primary key in it (I'll put the whole script at the end so you can copy and paste it into Query Analyzer). That CREATE TABLE statment looks like this:

CREATE TABLE #RowNumber (
RowNumber int IDENTITY (1, 1),
emp_id char(9) )
The RowNumber column is the IDENTITY column. It will start counting at one and increment each row by one. I only stored the primary key of the table that I wanted to number. I always prefer to copy as little data as possible for performance sake. Now I need to populate the temporary table. I can do this in a single statment like this:

INSERT #RowNumber (emp_id)
SELECT emp_id
FROM employee
WHERE job_id = 10
ORDER BY lname


I am inserting our emp_id's into the temporary table in order by last name. It's important to put the records into the temp table in the order you want them returned. And the last step is to return back the record set. I'll need to join back to the original table to get the values I didn't store in the temporary table. That query and the result set look like this:

SELECT RowNumber, e.emp_id, lname, fname, job_id
FROM #RowNumber r JOIN employee e
ON r.emp_id = e.emp_id
ORDER BY RowNumber


(4 row(s) affected)

RowNumber emp_id lname fname job_id
----------- --------- ------------------------------ -------------------- ------
1 A-C71970F Cruz Aria 10
2 PHF38899M Franken Peter 10
3 POK93028M Koskitalo Pirkko 10
4 MFS52347M Sommer Martin 10

(4 row(s) affected)
Since you'll use this to return results back to a client application we need to remove the lines that show the rowcounts. I use the SET NOCOUNT statement to do that. All that leaves me with this script:

SET NOCOUNT ON

CREATE TABLE #RowNumber (
RowNumber int IDENTITY (1, 1),
emp_id char(9) )

INSERT #RowNumber (emp_id)
SELECT emp_id
FROM employee
WHERE job_id = 10
ORDER BY lname

SELECT RowNumber, e.emp_id, lname, fname, job_id
FROM #RowNumber r JOIN employee e
ON r.emp_id = e.emp_id
ORDER BY RowNumber

DROP TABLE #RowNumber

SET NOCOUNT OFF


Notice that I also remembered to drop my temp table at the end. This would go very nicely inside a stored procedure. This should also be a very fast SQL script. The INSERT statement that populates the temp table is only making a copy of a small set of data. It is also doing this in memory. The SELECT statement also pulled the data pages from the employee table into memory. This means the second SELECT that actually returns the result set should also be very fast.

The only other approach that I know of is to count the records on the client side. In your loop that displays the records you could easily build a counter and use that instead.

UPDATE on 12/13/00:

A reader suggested the following query:

SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname


This is an interesting option that bears a little closer scrutiny. It runs a count of records that have lname less than or equal to the current lname value. This works as long as lname is unique. Otherwise you get duplicate row numbers. Let's rewrite this using the primary key:

SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id


Notice that I also had to change the ORDER BY clause. The row number field will always be in the order of the primary key. In summary this is a good solution if you have a field that uniquely identifies a record AND you want to order by that record. Thanks for the feekback kind reader.

-graz


Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Returning a week number for any given date and starting fiscal month (2 May 2007)

Other Recent Forum Posts

SQL select Top 10 records for unique combination of two columns (13h)

SSRS Report Sorting with Grouping Issue (1d)

ORA-01476: divisor is equal to zero (1d)

Create new columns based on min and max values of a record with multiple rows (1d)

Memory Required for Reporting Services 2022 (1d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

sUMMING MULTIPLE COLUMNS (3d)

- Advertisement -