adding row number to a query result

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

General SQL Server Forums

New to SQL Server Programming

adding row number to a query result

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-22 : 17:41:42

Is there any way I can add record number to my query result?

Exp:

Name Add tel
A 1 st 123456
B H ave. 987456

I need:
No Name Add tel
1 A 1 st 123456
2 B H ave. 987456

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 17:44:27

Are you using SQL Server 2005?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-22 : 17:46:53

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-22 : 19:50:43

Here is one way:


select rownumber = (select count(*) from sysobjects sq where sq.id <= so.id)
	,name
from	sysobjects so
order by id

you could also put the results into a #temp table that has an identity column defined then select out of the #temp table.

Be One with the Optimizer
TG

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-23 : 11:02:01

I don’t think this can help me TG. I don’t have id column or any other column that contains numbers.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-23 : 11:15:01

<sigh>


declare @t table (Name varchar(10), Addr varchar(10), tel varchar(10))
insert @t
select 'A', '1 st', '123456' union all
select 'B', 'H ave.', '987456'

select rownumber = (select count(*) from @t sq where sq.name <= so.name)
	,name
       ,Addr
       ,tel
from	@t so
order by name

output:
rownumber   name       Addr       tel
----------- ---------- ---------- ----------
1           A          1 st       123456
2           B          H ave.     987456

Be One with the Optimizer
TG

Sep410
Posting Yak Master

117 Posts

Posted - 2008-05-23 : 11:26:11

Dear TG,
I have 1124 records in my table it is not possible to do the first part that you mentioned.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-23 : 11:43:20

The first part is just to illustrate the problem. Run the entire thing to see how it works. Then use just the last query and modify it for your objects.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

jdippner
Starting Member

1 Post

Posted - 2008-05-23 : 11:49:06

quote:
Originally posted by Sep410

Dear TG,
I have 1124 records in my table it is not possible to do the first part that you mentioned.

Sep,
This should give you what you need. It is a trivial example using the sysobjects table, but essentially you create a temp table to hold the columns of your original column + a column for the row count. Then you do an insert/select to populate the temp table. Lastly, you query from the temp table.

declare @t table (
	row_id	int identity (1,1),
	name	varchar(50),
	id		int)

insert into @t(name, id)
select name, id from sysobjects
order by id

select * from @t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-26 : 05:02:22

quote:
Originally posted by Sep410

Is there any way I can add record number to my query result?

Exp:

Name Add tel
A 1 st 123456
B H ave. 987456

I need:
No Name Add tel
1 A 1 st 123456
2 B H ave. 987456

Thanks

Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources