increment a count i sql statement

[timc]

How can I add an incremented counter to a select statement.
Such as select last_name,date,Count? from table, so I would
get

miles,1/1/2003,1
long,1/3/2003,2
smith,1/3/2003,3

[sdives]

The only way I could get it to work was to create a work table with an identity column.

CREATE TABLE #wk (
last_name varchar (30) NULL ,
ctr int IDENTITY
) ON [PRIMARY]
GO

INSERT INTO #wk
select last_name from my_table

select last_name, getdate(), ctr from #wk

Hope it helps

Sidney Ives
Database Administrator
Sentara Healthcare

[timc]

Sorry should have been more clear, the table is already defined, and I can't change it. I need the select to increment

[rmiao]

Try select them into staging table with identity column.

[sdives]

You aren't modifying the original table. You create a temporary table with the same columns produced by the original query. Code the query so that it inserts into the work table. The insert will force the identity column to increment. Then simply select the data from the work table.

I don't think you can do a 'select into' and create an identity column at the same time.

Sidney Ives
Database Administrator
Sentara Healthcare

[sdives]

There is one other way and that would be to create a cursor. Use the original query (minus the incremented column). Create a counter variable and then display the columns along with the counter variable.

Sidney