Generate A AutoNumber

[lfcmd]

HI All
When i run a view from sqlserver i would like for it to return a autonumber against each item selected

How would i go about this

Simple Example

Table setup

Name Age
M 18
C 19
D 18
F 21
G 18

Code:
Select * from table where age ='18'


Result i would like to see would be as follows

AutoNum Name Age
1 M 18
2 D 18
3 G 18


Any help grately appreciated

 

[SQLDenis]

in SQL Server 2005 use ROW_NUMBER
in 2000 do something like this

create table #temp(AutoNum int identity, Name varchar(49) Age int)
insert #temp
Select name,age from table where age ='18'--where is the where clause??

select * from #temp
order by AutoNum

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

forgot a comma before age

create table #temp(AutoNum int identity, Name varchar(49), Age int)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Or you can do a 'running' count if you have unique values

example
use pubs

select (select count(*) from authors where au_id <= a.au_id) as RowNumber,* from authors a
order by au_id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lfcmd]

You cant create a Temp Table when using a view in sql server 2005 ?,

The reason i need this is, i am running one view against another, and need to be able to check a record against the next record in sequence

 

[SQLDenis]

lookup ROW_NUMBER in BOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lfcmd]

Thansk for that Denis

 

[SQLDenis]

np

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/