Adding row numbers to results

If you have something like
select au_fname from authors order by au_fname
You get:
au_fname
--
Abraham
Akiko
Albert
Ann
Anne
Burt
Charlene
Cheryl
Dean
Dirk
Heather
Innes
Johnson
Livia
Marjorie
Meander
Michael
Michel
Morningstar
Reginald
Sheryl
Stearns
Sylvia
(23 row(s) affected)
is there a way to make the result put row numbers in front of the results?
Such as:
au_fname
--
1. Abraham
2. Akiko
3. Albert
4. Ann
...
Thanks,
TomYou can create a temorpary table variable
Declare @.Out Table
(RowID Integer Primary Key Identity Not Null,
fName VarChar(50))
Insert the data from your query into this table variable...
Insert @.Out(fName)
Select au_fname
from authors
order by au_fname
And then output the data from the table variable...
Select * From @.Out
Altogether Now...
Declare @.Out Table
(RowID Integer Primary Key
Identity Not Null,
fName VarChar(50))
-- --
Insert @.Out(fName)
Select au_fname
from authors
order by au_fname
-- --
Select * From @.Out
"tshad" wrote:

> If you have something like
> select au_fname from authors order by au_fname
> You get:
> au_fname
> --
> Abraham
> Akiko
> Albert
> Ann
> Anne
> Burt
> Charlene
> Cheryl
> Dean
> Dirk
> Heather
> Innes
> Johnson
> Livia
> Marjorie
> Meander
> Michael
> Michel
> Morningstar
> Reginald
> Sheryl
> Stearns
> Sylvia
> (23 row(s) affected)
> is there a way to make the result put row numbers in front of the results?
> Such as:
> au_fname
> --
> 1. Abraham
> 2. Akiko
> 3. Albert
> 4. Ann
> ...
> Thanks,
> Tom
>
>|||http://www.aspfaq.com/2427
http://www.aspfaq.com/
(Reverse address to reply.)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uaE6X#cJFHA.4056@.TK2MSFTNGP14.phx.gbl...
> If you have something like
> select au_fname from authors order by au_fname
> You get:
> au_fname
> --
> Abraham
> Akiko
> Albert
> Ann
> Anne
> Burt
> Charlene
> Cheryl
> Dean
> Dirk
> Heather
> Innes
> Johnson
> Livia
> Marjorie
> Meander
> Michael
> Michel
> Morningstar
> Reginald
> Sheryl
> Stearns
> Sylvia
> (23 row(s) affected)
> is there a way to make the result put row numbers in front of the results?
> Such as:
> au_fname
> --
> 1. Abraham
> 2. Akiko
> 3. Albert
> 4. Ann
> ...
> Thanks,
> Tom
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:enIr27dJFHA.3340@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2427
I actually did do the asp.net route, which was fairly easy, but it is good
to know the other ways as I am sure there are cases where it would be better
to get it from the database.
Thanks,
Tom
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:uaE6X#cJFHA.4056@.TK2MSFTNGP14.phx.gbl...
>|||> I actually did do the asp.net route, which was fairly easy, but it is good
> to know the other ways as I am sure there are cases where it would be
better
> to get it from the database.
Like what? This number is just for presentation.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ehncValJFHA.656@.TK2MSFTNGP14.phx.gbl...
> better
> Like what? This number is just for presentation.
If I were filling a Grid and not writing code when my page loads to go
through the grid, it might be easier to just let Sql give it to me, for
example.
Tom