Link to home
Start Free TrialLog in
Avatar of ralph_rea
ralph_rea

asked on

SQL Server 2000: sequential number

Hi,
I've a SQL Server query like this:


select a.id, a.desc_id, b.id_num
from tab_a a, tab_b b
where a.id=b.id

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
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.id, a.desc_id, b.id_num, row_number() over (order by b.id)
from tab_a a, tab_b b
where a.id=b.id
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/kb/186133
Avatar of ralph_rea
ralph_rea

ASKER

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

but I get number=1 for all rows

this link:
http://thetrainerph.spaces.live.com/Blog/cns!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 a.id =b.id
WHERE a.ID <= reg.ID AND b.ID <= reg1.ID
) AS 'Row Number'
, reg.ID,reg1.ID
FROM
tab_a reg
inner join tab_b reg1 on reg.id =reg1.id
select row_number() over (order by b.id) as rank,
a.id, a.desc_id, b.id_num
from tab_a a, tab_b b
where a.id=b.id
Angelgeo,
row_number()  is for SQL Server 2005, I have SQL Server 2000
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
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.id, a.desc_id, b.id_num,
       (select COUNT(*) from tab_a a1 join tab_b b1 on a1.id=b1.id and a1.desc_id <= a.desc_id) as row_num
  from tab_a a
  join tab_b b on a.id=b.id
 
select
(select sum(1)
from tab_a
where id<=a.id
) as 'Row Number',
 a.id,
 a.desc_id,
 b.id_num
from
  tab_a a,
  tab_b b
where a.id=b.id
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))

INSERT INTO @TempTable
select a.id, a.desc_id, b.id_num
from tab_a a, tab_b b
where a.id=b.id

SELECT * FROM @TempTable