MS SQL Server MSEQ - Identity or Sequence column in non-table SELECT ?

Author

Identity or Sequence column in non-table SELECT ?

GlennM

2005-06-02, 8:24 pm

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 ?

Hari Prasad

2005-06-02, 8:24 pm

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@microso
ft.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 ?
>
>

Hugo Kornelis

2005-06-02, 8:24 pm

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)

Sponsored Links