Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help me to select ROW_NUMBER in sql server 2000 Expand / Collapse
Author
Message
Posted Wednesday, October 15, 2008 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 11, 2009 6:33 AM
Points: 2, Visits: 6
help me to select ROW_NUMBER in sql server 2000
Post #586011
Posted Wednesday, October 15, 2008 2:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
there is no row_number () in sql 2000.

"Keep Trying"
Post #586018
Posted Wednesday, October 15, 2008 4:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:07 AM
Points: 329, Visits: 468
Miss Qureshi (10/15/2008)
help me to select ROW_NUMBER in sql server 2000

Why do you want to have row_number?
Do you want to simply generate serial no for the recordset?
If so, do this numbering in the front end application if you use




Madhivanan

Failing to plan is Planning to fail
Post #586063
Posted Wednesday, October 15, 2008 1:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 23, 2008 1:43 PM
Points: 89, Visits: 206
Hello,

Another way to have a row number is to use the alter table command and add a rowId int identity field to the table ( unless it already has an identity field ).

When you add the rowId column, it will create the column and populate it with a row number.

like :

alter table yourTableName
add rowId int identity

Regards,

Terry
Post #586509
Posted Thursday, August 12, 2010 10:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
You can fake it with horrible, expensive code.

SELECT.. (SELECT COUNT(*) FROM Foobar AS F2 WHERE F2.x <= F1.x) As row_number
FROM Foobar AS F1
WHERE ..

Do not use an IDENTITY!! ROW_NUMBER() is a function and not a table property; it changes every time you invoke it.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #968381
Posted Saturday, August 21, 2010 1:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
I disagree with the idea of NOT using an IDENTITY for such a purpose if it's on a Temp Table.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #972987
Posted Thursday, April 07, 2011 10:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 20, 2011 6:47 AM
Points: 2, Visits: 35
i usually just place what is needed into a temp table as below:

select
RowNumber = identity(int,1,1)
,Column_1
,Column_2
into #im_temp0
from <tableName>
Post #1090049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse