Link to home
Start Free TrialLog in
Avatar of ralph_rea

asked on

SQL Server 2000: sequential number

I've a SQL Server query like this:

select, a.desc_id, b.id_num
from tab_a a, tab_b b

I need add a row number at this query, that returns the sequential number of a row starting at 1 for the first row.

In SQL Server 2005 I know that exists ROW_NUMBER function but I need a similar function in SQL Server 2000.

How Can I add a sequential number (like identity) in my query?

Thanks in advance!
Avatar of RiteshShah
Flag of India image

there is no inbuilt facility in SQL Server 2000, you can do it logically, copy the result of query in tempTable with SELECT....INTO query and add identity column in temp table.
Avatar of Kakhaber Siradze
select, a.desc_id, b.id_num, row_number() over (order by
from tab_a a, tab_b b
How to dynamically number rows in a SELECT Transact-SQL statement
Avatar of ralph_rea


I add:
select rank=count(*),, a.desc_id, b.id_num
from tab_a a, tab_b b
group by, a.desc_id, b.id_num
order by rank

but I get number=1 for all rows

this link:!9F717AF2A2401F0F!639.entry

is correct for a single table

Have someone anyidea?
try this

SELECT distinct ( SELECT SUM(1)
FROM tab_a a
inner join tab_b b on
WHERE a.ID <= reg.ID AND b.ID <= reg1.ID
) AS 'Row Number'
, reg.ID,reg1.ID
tab_a reg
inner join tab_b reg1 on
select row_number() over (order by as rank,, a.desc_id, b.id_num
from tab_a a, tab_b b
row_number()  is for SQL Server 2005, I have SQL Server 2000
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try this query.

select, a.desc_id, b.id_num,
       (select COUNT(*) from tab_a a1 join tab_b b1 on and a1.desc_id <= a.desc_id) as row_num
  from tab_a a
  join tab_b b on
(select sum(1)
from tab_a
where id<
) as 'Row Number',,
  tab_a a,
  tab_b b
You seem adverse to temporary tables but with table variables (which are essentially temp. tables) their scope is only for the duration of the batch/query so you have less to worry about with respect to managing it, it's gone as soon as the query completes.

Just use your original query to populate it, add an ORDER BY to get it in the right sequence maybe, then SELECT out of the table variable, voila.

DECLARE @TempTable table([id] INT, desc_id VARCHAR(50), id_num INT, RowNumber INT IDENTITY(1,1))

select, a.desc_id, b.id_num
from tab_a a, tab_b b

SELECT * FROM @TempTable