Return row number with Query - 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

General SQL Server Forums

New to SQL Server Programming

Return row number with Query

Author

Topic

xcas08
Starting Member

13 Posts

Posted - 2008-11-27 : 11:07:28

Hello SQL Team,

I'm trying to write a query that will return the row number of the data to which it belongs. For example, i have a query that returns 4098 rows of data. I would like a column that would display the row id for each record. Much like Excel does on their spreadsheets. Thanks for your help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 11:10:17

You can use Row_number()to generate if you in SQL 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-27 : 11:38:29

quote:
Originally posted by xcas08

Hello SQL Team,

I'm trying to write a query that will return the row number of the data to which it belongs. For example, i have a query that returns 4098 rows of data. I would like a column that would display the row id for each record. Much like Excel does on their spreadsheets. Thanks for your help.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114789

onlyforme
Starting Member

25 Posts

Posted - 2008-11-28 : 04:16:46

hi
try this
select row_number() over(order by id) as slno,* from test

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-11-28 : 04:25:21

hi all,
how do we tackle this in sql2000 server

ok tanx.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 05:37:40

quote:
Originally posted by soorajtnpki

hi all,
how do we tackle this in sql2000 server

ok tanx.....

you need to use subquery.something like

select (select count(*) from test where id<t.id)+1 as slno,* from test t

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-11-28 : 06:25:12

ok then
for the sample below

id field
1 data1
2 data2
3 data3

what will be row_number for record with id =1 ?

ok tanx....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 06:32:22

quote:
Originally posted by soorajtnpki

ok then
for the sample below

id field
1 data1
2 data2
3 data3

what will be row_number for record with id =1 ?

ok tanx....

depending on what order you generate the number. whats the order you want generate number? based on id or field?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 03:11:50

quote:
Originally posted by xcas08

Hello SQL Team,

I'm trying to write a query that will return the row number of the data to which it belongs. For example, i have a query that returns 4098 rows of data. I would like a column that would display the row id for each record. Much like Excel does on their spreadsheets. Thanks for your help.

If you want to show data in 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