Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table.
Example: select <...>, state from tbl_states (<...> is what I am asking for)
Col1 Col2
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?
Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
David Portas
SQL Server MVP
|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>