Re: Identity or Sequence column in non-table SELECT ?

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D4FDC49B-262B-49CB-9789-E9AE4570FEA9@xxxxxxxxxxxxxxxx
> 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 ?
>
>


.

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)