January 8, 2008 at 11:02 pm
hi friends
i am using following row number function in my sql server 2005 :
select appno,row_number() over(partition by appno order by appno) from tab
which is giving following result :
100 1
100 2
200 1
200 2
200 3
300 1
300 2
300 3
but now i want the same result in sql server 2000
but this row number function not supported in sql 2000
so plz give me any alternate solution for this....
January 8, 2008 at 11:29 pm
amol_j_kothawade (1/8/2008)
hi friendsi am using following row number function in my sql server 2005 :
select appno,row_number() over(partition by appno order by appno) from tab
which is giving following result :
100 1
100 2
200 1
200 2
200 3
300 1
300 2
300 3
but now i want the same result in sql server 2000
but this row number function not supported in sql 2000
so plz give me any alternate solution for this....
declare @t table(appno int, i int identity(1,1))
insert into @t
select 100 union all select 100 union all select 200 union all select 200
union all select 200 union all select 300 union all select 300 union all select 300
--method 1
select t1.appno,
(select count(*) from @t where appno=t1.appno and i<=t1.i)
from @t t1
--method 2
select t1.appno,t1.i-t2.i+1 from @t t1 inner join
(
select appno,min(i) as i from @t
group by appno
) as t2
on t1.appno=t2.appno
But if you want to show the data in front end application, start a counter, increment for each appno, reset to 1 when appno changes
If you use Crystal reports, Group the report by appno,make use of running total feature with count and reset to each appno
Failing to plan is Planning to fail
May 16, 2008 at 6:46 am
select *, ( select count(*)
from addOrder counter
where counter.value <= addOrder.value) as rowNumber
from addOrder
--grouped
select *, ( select count(*)
from addOrder counter
where counter.groupNumber = addOrder.groupNumber
and counter.value <= addOrder.value) as rowNumber
from addOrder
May 16, 2008 at 6:57 am
[font="Verdana"]
...row number function not supported in sql 2000
Row_Number() is one of the new feature introduced in SQL Server 2005.
Mahesh[/font]
MH-09-AM-8694
December 23, 2008 at 1:36 pm
Editor's note: Post edited to remove insults.
December 23, 2008 at 1:40 pm
Please do not post insults or personal attacks in these forums.
June 4, 2010 at 6:49 am
create table t
(
id int identity (1,1),
i int,
a varchar(10)
)
Select id,i, a, Row_Order=(select count(T1.i) + 1
from t T1
where T1.id < T.id and t1.a = t.a
)
from t T
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply