How to include Record Number in Query Result 1

[Andel]

Is there a way to include 'record number' in a query result? Example of a table:

FirstName LastName
--------- ----------
Mark Smith
John Doe
Mary Williams
Andrew Johnson

The query result that I want is this...

Record# FirstName LastName
------- --------- ----------
1 Mark Smith
2 John Doe
3 Mary Williams
4 Andrew Johnson

Thank you in advance!




Andel
maingel@hotmail.com

 

[ludmart]

Assume your table was called YourFirstNameLastNameTable. You may first select into a temporary table, with an Identity column added for the Record# column, then select from this temp table:

SELECT [Record#] = IDENTITY(int, 1, 1), FirstName, LastName from YourFirstNameLastNameTable into #TempTable from Customer
SELECT * from #TempTable

 

[Andel]

It works! Thank you!
Andel
maingel@hotmail.com

 

[Andel]

-- here's to insert a record number on every row --
-- there has to be a unique key. in this case lastnm is unique. --

select LastNm,
'RecNo' = (select Count(LastNm) from Names N where n.LastNm <= Names.LastNm)
from Names
order by recno
Andel
andelbarroga@hotmail.com

http://andyb.8m.com