the row no in query output

Question

I have a numeric field (say num) in table along with pkey.

select * from mytable order by num

now how I can get the row no in query output of a particular row for which I have pkey.

I'm using sql 2000.

Answer 1

as i understand your question you want to get the number of all rows returned, right? if so use @@rowcount

Answer 2

Sounds like you want a row number for each record returned.

In SQL 2000, you can either do this:

SELECT (SELECT COUNT(*) FROM MyTable t2 WHERE t2.num <= t.num) AS RowNo, *
FROM MyTable t
ORDER BY num

which assumes num is unique. If it's not, then you'd have to use the PK field and order by that.

Or, use a temp table (or table var):

CREATE TABLE #Results
(
RowNo INTEGER IDENTITY(1,1),
MyField VARCHAR(10)
)

INSERT #Results
SELECT MyField
FROM MyTable 
ORDER BY uum

SELECT * FROM #Results

DROP TABLE #Results

In SQL 2005, there is a ROW_NUMBER() function you could use which makes life a lot easier.

Answer 3

As Ada points out, this task became a lot easier in SQL Server 2005....

SELECT whatever, RowNumber from (
     SELECT pk
           , whatever
          , ROW_NUMBER() OVER(ORDER BY num) AS 'RowNumber'
    FROM mytable
)
WHERE pk = 23;