identity in the resultset

i want to generate a column in the resultset
that works like an identity column.
one way i thought is creating a temp table with an identity column
and inserting resultset to that table and select again.
is there any other way that works serverside?
thanks...Using a temp table or a table variable *is* done on the server.
Is there a special reason for using identity? Are you trying to sort or rank
the rows in the result-set? If so, there are built-in functions in SQL2005
and several custom options for SQL2000.
ML|||Always specify SQL Server version you are using when posting questions.
With SQL 2005, you can use ROW_NUMBER() as described in the Books Online.
Another method with older versions is with a subquery that includes a unique
column(s) and the same criteria as the main table query:
SELECT MyData,
(SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
MyRowNumber,
FROM MyTable t1
ORDER BY Id
Personally, I'd assign the numbers in the client application.
Hope this helps.
Dan Guzman
SQL Server MVP
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:OiAOowO9FHA.472@.TK2MSFTNGP15.phx.gbl...
>i want to generate a column in the resultset
> that works like an identity column.
> one way i thought is creating a temp table with an identity column
> and inserting resultset to that table and select again.
> is there any other way that works serverside?
> thanks...
>
>|||i wanted to say , is there any other way that works serverside too?
can you tell about custom options for SQL2000?
thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
> Using a temp table or a table variable *is* done on the server.
> Is there a special reason for using identity? Are you trying to sort or
> rank
> the rows in the result-set? If so, there are built-in functions in SQL2005
> and several custom options for SQL2000.
>
> ML|||http://www.aspfaq.com/2427
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:uUIfc6O9FHA.3636@.TK2MSFTNGP09.phx.gbl...
>i wanted to say , is there any other way that works serverside too?
> can you tell about custom options for SQL2000?
> thanks
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:02F1360F-284E-4DCD-A8FF-A6EDD07825CD@.microsoft.com...
>|||
> Another method with older versions is with a subquery that includes a
> unique
> column(s) and the same criteria as the main table query:
> SELECT MyData,
> (SELECT COUNT(*) + 1 FROM MyTable t2 WHERE t2.Id < t1.Id) As
> MyRowNumber,
> FROM MyTable t1
> ORDER BY Id
thanks for that.|||Dan posted one, a few can be found here:
Row-numbering:
http://www.aspfaq.com/show.asp?id=2427
Paging:
http://www.aspfaq.com/show.asp?id=2120
ML