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!
Microsoft SQL ServerSSRSMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
MTillett

8/22/2022 - Mon
RiteshShah

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.
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
rashmi_vaghela

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
tipsybroom

How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/kb/186133
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?
rashmi_vaghela

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kakhaber Siradze

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
ralph_rea

ASKER
Angelgeo,
row_number()  is for SQL Server 2005, I have SQL Server 2000
ASKER CERTIFIED SOLUTION
Sharath S

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sharath S

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
 
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Kakhaber Siradze

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
MTillett

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