row number in Select statment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

row number in Select statment

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-04 : 13:58:10

sam writes "I am using SQL Server 7.0
I want display the row number in the select statment. I don't have an identity coloumn in the table and I don't want to add one. I don't want to create a temperory table for this.
So how can select the row number in the select statment.

Eg. Select name from employee

I need to get the result as below

1 SAM
2 PAUL
3 MIKE

My table structure is

name varchar(50)
sal int
dob datetime

Thanks
SAM"

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-04 : 15:02:56

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q186133&SD=MSKB&

-Chad

Jay99

468 Posts

Posted - 2002-03-04 : 15:04:22

I can understand not wanting to add an identity column, but why the 'no temp table' constraint? That makes this pretty hard (impossible?). Remember, sql is set based, not iterative. So something like . . .


select @counter++, name
from employee

... is not going to do what you want.

Jay

EDIT: ok, snipped and schooled :) . . . just remember, you will need to deal with dups.

Edited by - Jay99 on 03/04/2002 15:08:09

sam_varg007
Starting Member

8 Posts

Posted - 2002-03-07 : 11:29:20

Thanks chadmat

pothireddy77
Starting Member

3 Posts

Posted - 2002-03-11 : 07:43:27

create table #temp (id1 numeric identity(1,1),empname varchar(50),sal int,dob datetime)
insert into #temp(empname,sal,dob) select empname,sal,dob from employee
select * from #temp

pothireddy77
Starting Member

3 Posts

Posted - 2002-03-11 : 07:44:37

create table #temp (id1 numeric identity(1,1),empname varchar(50),sal int,dob datetime)
insert into #temp(empname,sal,dob) select empname,sal,dob from employee
select * from #temp

Nazim
A custom title

1408 Posts

Posted - 2002-03-11 : 07:47:00

Add a drop table #temp

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources