how to get the row number

Question

how to get the row number

Manuel Rodriguez-251202

Newbie

Points: 8
More actions
August 8, 2005 at 7:26 am

#186884

I use sql server 2000

I want to do the following

Select first_name from customers
I want the output to be

rownumber    first_name
----------     ----------
1                 Peter
2                 Carlos
3                 Sean
..                 .....

How do I tell sql server to print
rownumber?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

SQL Server 2000 does not have that functionality built-in, like SQL Server 2005 will. You could do it using for instance a temp table where you insert the result rows and add an identity column, then select from that. But I would recommend you do rownumbers on the client.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

You don't... do it client side. Easier to do and it's the right thing to not make the server do this.

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 3

SELECT IDENTITY (INT, 1, 1) seq, first_name INTO #cust2 FROM customers

SELECT * FROM #cust2 ORDER BY seq

Not efficient, but effective.

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 4

G'day all,

A word of caution might be in order. Row Numbers are often requested so that the UI can attempt to update that particular row in the DB. However, the nature of the DB is such that there is no guarantee that the same query executed twice in a row will generate the same sequence of rows. Translated, that means that row 1 in the first run may be row 58 in the second run, and yet some other row in the third run. Be careful what you attempt to do with the row number after you expose it to the end user. The results may not be as expected.

(I apologize if this seems too obvious to most. However, this is in the sql server newbies forum.)

Have a great day!

Wayne

Manuel Rodriguez-251202 Newbie Points: 8 More actions · Answer 5

Manuel Rodriguez-251202

Newbie

Points: 8

August 8, 2005 at 12:28 pm

#580503

Thanks

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

Good advice, and yes it is always worth pointing out. As I said in another thread, if we start using physical properties telling the DBMS how to access the data instead of just which data we want, we can just as well stop using a relational DBMS.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 7

My two cents: It's relative to a lot of things, including whether your DBMS is busy busy busy, or whether it sits there bored all day like the Maytag repairman. It's also relative to whether it's worth finding a better way to do it -- if it's only a "run once", who cares? Databases are pretty neat, but this isn't eschatology. Sometimes finding the precisely correct way to do something is the best way to go. Sometimes you're better off handing the user something that works, quick.

I just know I'm going to get in trouble for saying that!