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