By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

Row sequence number

P: n/a
Hello all,
Im currently using a SQL Serve 2K. Would like to do a select
which returns the row number - this should not be physically stored in
the database. So for example, I would like to do a query against the
CUSTOMER table and receive:

* rowID || name
1 Evander
2 Ron
3 Scoth
4 Jane

I dont want to store the ID, because if I change the order by
clause, the sequence may modifiy, and, for another example, having the
same set of data, I would receive:

* rowID || name
1 Scoth
2 Ron
3 Jane
4 Evander

could someone help me ?

best regards,
Evandro

Apr 4 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.
could someone help me ? <<


You can help yourself by doing about one week's worth of reading on
RDBMS. You are making a fool of yourself by not knowing the basics.

Apr 4 '06 #2

P: n/a
SQL 2000 does not have this pseudo-column. It is introduced in 2005.
The only way I know how to way around it is to dump your query into
temp. table that has anextra column (let's call it rowID ) which is an
auto-increment.

Apr 4 '06 #3

P: n/a
Sergey (af******@gmail.com) writes:
SQL 2000 does not have this pseudo-column. It is introduced in 2005.
The only way I know how to way around it is to dump your query into
temp. table that has anextra column (let's call it rowID ) which is an
auto-increment.


Neither does SQL 2005 have any pseudo-column. row_number() is a function,
and you can set it up so that it restarts on some defined partition.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 4 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.