Row IDs in resultset

Hi,
I would like to get Row IDs to number my resultset from 1 to whatever. For
example, if I have 10 records from the following statement :
select FirstName, LastName from employees
I would like to number the records from 1 to 10. How do I do it? No cursor
please.
TIAhttp://www.aspfaq.com/show.asp?id=2427
Note, this does not include any information about SQL Server 2005's
ROW_NUMBER function, which makes this whole process much easier...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||> http://www.aspfaq.com/show.asp?id=2427
> Note, this does not include any information about SQL Server 2005's
> ROW_NUMBER function, which makes this whole process much easier...
Hey man, how many hands do you think I have? :-)|||Based on the amount of information on the site, I'd say somewhere between
four and six?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXowA684FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hey man, how many hands do you think I have? :-)
>|||Method 1:
If one of the columns in query is unique, the following calculates a
sequential 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
Method 2:
If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||More like 17. :)
ML