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