Increment each record returned by 1

[DH]

Hello,

I am writing a query to return results to use in a export file. One of the columns is I need to manually create is 'assetID' and per the specs, I need to:

"Starting with the value of 1, increment 'assetID' for each record created.

For simple example the following:

USE Northwind
SELECT FirstName
FROM Employees
WHERE title = 'Sales Representative'

Returns the following results:

FirstName
Nancy
Janet
Margaret
Michael
Robert
Anne

How can I insert a column named 'AssetID' to the left of FirstName and then increment by 1 for each record returned?

AssetID FirstName
1 Nancy
2 Janet
3 Margaret
4 Michael
5 Robert
6 Anne

Thank you,

Dave

 

[gmmastros]

The simplest way is to create a table variable that has a feld defined as integer identity.

USE Northwind

SET NOCOUNT ON

declare @Temp Table(AssetId Integer Identity(1,1), FirstName VarChar(50)

Insert into @temp(FirstName)
SELECT FirstName
FROM Employees
WHERE title = 'Sales Representative'

Select * from @temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom