Row sequence number

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,
Evandrohttp://support.microsoft.com/defaul...b;EN-US;q186133

--
Anith|||Use Front end application to number the result

Madhivanan

Row sequence number

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,
EvandroLet'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.|||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.|||Sergey (afanas01@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

row number.

All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. I think I have done this in
the past but am a bit rusty after not using SQL for a while. If
anyone could help with a code snippet this would be gretly
appreciated.Here's an example from the Pubs database:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS rownum
,*
FROM Authors AS A

--
David Portas
SQL Server MVP
--|||
David Mackintosh wrote:
> All I am trying to do is return the row number (calculated field?)
> with each row returned from an SQL query. I think I have done this in
> the past but am a bit rusty after not using SQL for a while. If
> anyone could help with a code snippet this would be gretly
> appreciated.

There are now row numbers.

Zach|||>> All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. <<

Since this would have to be for display purposes in the front end, you
ought to be doing in the front, not the database.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!