row number

how or what function can we use in sql server 2005 or 2000 to return the
corresponding row number of a record in select statement
for example
SELECT name, xxxxx as Number
FROM TableX
xxxxx - is the function or keyword that returns the corresponding row number
of the select statement.
the result set could be..
Name Number
John Doe 0
Jane Doe 1
0, 1 are the corresponding row number..
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787http://www.aspfaq.com/2427

> John Doe 0
> Jane Doe 1|||Jose,
In SQL Server 2000, you can derive row numbers using sub query like
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
SQL Server 2005 has an inbuilt ROW_NUMBER function.
SELECT ROW_NUMBER() OVER (ORDER BY job_desc ASC) as rownum, * FROM jobs
For more info see
http://toponewithties.blogspot.com/...es.blogspot.com
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||Using a identity field you obtain it. If you haven't I think exists a
internal rowid or something like that which provide us the row number.
see you,
"Jose G. de Jesus Jr MCP, MCDBA" wrote:

> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row numb
er
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||In SQL Server 2005, look up the ROW_NUMBER() function.
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||> If you haven't I think exists a
> internal rowid or something like that
There are internal row identifiers but these are not exposed to you, so you
can't use them in display.
Typically, the best way to present row numbers is to tack them on in the
presentation layer, since that's the only place where you *have to* loop
through and handle every single row anyway. Forcing the row numbers to be
generated in the database puts unnecessary strain there and turns a simple
query into either a subquery that is evaluated per row, or a mess with
pre-population into a temp table or table variable.|||>Using a identity field you obtain it
That behavior is not guaranteed.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
> Using a identity field you obtain it. If you haven't I think exists a
> internal rowid or something like that which provide us the row number.
> see you,
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||http://support.microsoft.com/defaul...b;EN-US;q186133
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||If one of the columns in query is unique, the following calculates a
sequence number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||I disagree. While it is true that using the IDENTITY function in a SELECT
INTO with an ORDER BY clause doesn't guarantee that the order of the
IDENTITY values match the order specified in the ORDER BY clause, using an
INSERT...SELECT...ORDER BY to insert into a temporary table or table
variable with an IDENTITY column will always work correctly. See KB273586.
An obvious improvement, however, is the ROW_NUMBER() function in SQL Server
2005, which eliminates the need for the self-join or the intermediate temp
table or table variable.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OEtZ0dKqFHA.3192@.TK2MSFTNGP10.phx.gbl...
> That behavior is not guaranteed.
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
the
>