How to generate Row_numbers in Sql Server 2000?
-
Wednesday, May 30, 2007 5:43 PM
Hi
How to generate Row_numbers in Sql Server 2000
in Sql Server 2005 we are using
Select ROW_NUMBER() OVER(ORDER BY FieldName DESC) AS 'Row Number', FieldName from EMPtable
but I am expecting in Sql Server 2000
please advice
Thanks
Answers
-
Wednesday, May 30, 2007 9:19 PMModerator
Here are couple of examples. The basic idea is to use a subquery and count how many things are of a lesser (or greater value)
create table addOrder
(
value char(1),
groupNumber int
)
insert into addOrder
select 'A',1
union all
select 'B',1
union all
select 'C',1
union all
select 'D',2
union all
select 'E',2
go
--unique
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
value groupNumber rowNumber
----- ----------- -----------
A 1 1
B 1 2
C 1 3
D 2 4
E 2 5
value groupNumber rowNumber
----- ----------- -----------
A 1 1
B 1 2
C 1 3
D 2 1
E 2 2
All Replies
-
Wednesday, May 30, 2007 5:47 PM try this
Select IDENTITY(int,1,1) AS [Row Number]
, FieldName
INTO #Temp
from EMPtable
-- [edited]
order by
FieldName DESC
SELECT *
FROM #Temp
WHERE [Row Number] = 10
DROP TABLE #Temp
another option is to declare a table variable- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, August 24, 2010 12:01 PM
-
Wednesday, May 30, 2007 5:50 PMhere's the table variable sample
DECLARE @Temp TABLE (
[Row Number] int IDENTITY(1,1)
, FieldName varchar(100)
)
INSERT
INTO @Temp
Select FieldName
INTO #Temp
from EMPtable
SELECT *
FROM @Temp
WHERE [Row Number] = 10 -
Wednesday, May 30, 2007 8:11 PMModerator
See if this helps.
How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
AMB
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, August 24, 2010 12:01 PM
-
Wednesday, May 30, 2007 9:19 PMModerator
Here are couple of examples. The basic idea is to use a subquery and count how many things are of a lesser (or greater value)
create table addOrder
(
value char(1),
groupNumber int
)
insert into addOrder
select 'A',1
union all
select 'B',1
union all
select 'C',1
union all
select 'D',2
union all
select 'E',2
go
--unique
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
value groupNumber rowNumber
----- ----------- -----------
A 1 1
B 1 2
C 1 3
D 2 4
E 2 5
value groupNumber rowNumber
----- ----------- -----------
A 1 1
B 1 2
C 1 3
D 2 1
E 2 2 -
Tuesday, August 24, 2010 11:19 AM
Condition: The table from your select must have an identity column. For example IDColumn.
----------------------------------------------------------------------------------------
CREATE view vEMPtable
AS
Select FieldName , IDColumn from EMPtable
GROUP BY FieldName , IDColumn----------------------------------------------------------------------------------------
Select (Select count(*) from vEMPtable v1 Where v1.IDColumn < v.IDColumn ) + 1 AS Row_Number,
v.FieldName
FROM vEMPtable v----------------------------------------------------------------------------------------
I know, it is not good for select performance :D .Instead, if you are using the select in a stored procedure use a temporary table.
-
Tuesday, August 24, 2010 1:21 PMhttp://support.microsoft.com/kb/186133/en-us
http://www.t-sql.ru
.png){kind=spacer}
