Query help

29 Jun 2006 11:29 AM

Peter Newman

For reasons i wont go into i need to have an identity field when returning a
select statement

ie Select * from Table1 returnn

Field1    field2    etc
   aa         aa       aa
   bb         bb       bb

and i want

Field1    field2    etc   IDENT
   aa         aa       aa        0
   bb         bb       bb        1

any clues ?

29 Jun 2006 11:42 AM

Sha Anand

Assuming that you PK is field1 and the order of Id cols is also based on Field1

select
t1.*,
(select count(*) from table1 t2 where t2.Field1 < t1.Field1) as IdCol
from Table1 t1

- Sha Anand

Show quote

"Peter Newman" wrote:

> For reasons i wont go into i need to have an identity field when returning a
> select statement
>
> ie Select * from Table1 returnn
>
> Field1    field2    etc
>    aa         aa       aa
>    bb         bb       bb
>
> and i want
>
> Field1    field2    etc   IDENT
>    aa         aa       aa        0
>    bb         bb       bb        1
>
> any clues ?

29 Jun 2006 12:16 PM

Alejandro Mesa

How to dynamically number rows in a SELECT Transact-SQL statement
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

AMB

Show quote

"Peter Newman" wrote:

> For reasons i wont go into i need to have an identity field when returning a
> select statement
>
> ie Select * from Table1 returnn
>
> Field1    field2    etc
>    aa         aa       aa
>    bb         bb       bb
>
> and i want
>
> Field1    field2    etc   IDENT
>    aa         aa       aa        0
>    bb         bb       bb        1
>
> any clues ?