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