help me to select ROW_NUMBER in sql server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » help me to select ROW_NUMBER in sql server...

help me to select ROW_NUMBER in sql server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

Asma Qureshi

Posted Wednesday, October 15, 2008 1:44 AM

Forum 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

ChiragNS

Posted Wednesday, October 15, 2008 2:13 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

there is no row_number () in sql 2000.

"Keep Trying"

Post #586018

Madhivanan-208264

Posted Wednesday, October 15, 2008 4:09 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 5:17 AM
Points: 329, Visits: 461

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

tbeadle

Posted Wednesday, October 15, 2008 1:46 PM

SSC 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

CELKO

Posted Thursday, August 12, 2010 10:18 AM

SSCommitted

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

Jeff Moden

Posted Saturday, August 21, 2010 1:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 32,930, Visits: 26,819

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #972987

Stephan Redmon

Posted Thursday, April 07, 2011 10:29 AM

Forum 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 »

Permissions