Identity or Sequence column in non-table SELECT ?

I'm looking for a way to specify a column in a SELECT statement
that is a sequence number related to the record number in the result set.
Ideally, just a number from 1 to N.
Example:
Select <?> as Sequence, column1, column2 from table1
The Identity function can do this, but only for SELECT INTO
a new table. I don't want to create a new table.
The NewID() function returns a new global ID, but those
are not sequential.
I was hoping there might be some special variable @.@.XXX
that represents the row number in the results, but could not find
anything like that in the SQL Server documentation.
Any ideas ?
Hi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Note:
This aproach is not recommended on a huge table. This query takes long time
and take more resource.
Thanks
Hari
SQL Server MVP
"GlennM" <GlennM@.discussions.microsoft.com> wrote in message
news:D4FDC49B-262B-49CB-9789-E9AE4570FEA9@.microsoft.com...
> I'm looking for a way to specify a column in a SELECT statement
> that is a sequence number related to the record number in the result set.
> Ideally, just a number from 1 to N.
> Example:
> Select <?> as Sequence, column1, column2 from table1
> The Identity function can do this, but only for SELECT INTO
> a new table. I don't want to create a new table.
> The NewID() function returns a new global ID, but those
> are not sequential.
> I was hoping there might be some special variable @.@.XXX
> that represents the row number in the results, but could not find
> anything like that in the SQL Server documentation.
> Any ideas ?
>
|||On Thu, 2 Jun 2005 12:44:02 -0700, GlennM wrote:

>I'm looking for a way to specify a column in a SELECT statement
>that is a sequence number related to the record number in the result set.
>Ideally, just a number from 1 to N.
>Example:
>Select <?> as Sequence, column1, column2 from table1
>The Identity function can do this, but only for SELECT INTO
>a new table. I don't want to create a new table.
>The NewID() function returns a new global ID, but those
>are not sequential.
>I was hoping there might be some special variable @.@.XXX
>that represents the row number in the results, but could not find
>anything like that in the SQL Server documentation.
>Any ideas ?
>
Hi Glenn,
http://www.aspfaq.com/show.asp?id=2427
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)