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!
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
Last Comment
MTillett
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.
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
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!
Walt Forbes
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/kb/186133
http://support.microsoft.com/kb/186133
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?
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 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.
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
a.id, a.desc_id, b.id_num
from tab_a a, tab_b b
where a.id=b.id
ASKER
Angelgeo,
row_number() is for SQL Server 2005, I have SQL Server 2000
row_number() is for SQL Server 2005, I have SQL Server 2000
ASKER CERTIFIED SOLUTION
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
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
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 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
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
(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
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