How to get the Row No - 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

How to get the Row No

Author

Topic

mageshks
Yak Posting Veteran

59 Posts

Posted - 2006-06-17 : 07:23:27

Hi

How to get the row number like we have in ROWNUM . I want my result set like the following

Sl.no Mark
-------------------

1 10

2 20

3 30

Thanks

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-17 : 07:37:56

Unless you have a need for it in the query, then do that in the front end. If you have that need then you have to give more information.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-17 : 07:58:50

quote:
Originally posted by mageshks

How to get the row number like we have in ROWNUM . I want my result set like the following

Sl.no Mark
-------------------
1 10
2 20
3 30

A quick'n'dirty solution is to create a temporary table or a table variable with identity column. Insert from source table into newly created temp/variable table in the right order and then output from this.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-17 : 19:48:07

[code]declare @table table
(
Mark int
)

insert into @table
select 10 union all
select 20 union all
select 30

select (select count(*) from @table x where x.Mark <= t.Mark) as row_no,
Mark
from @table t[/code]

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-06-18 : 19:40:27

SQL Server 2005 has row_number and some ranking operators if that's an option

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-19 : 01:34:54

quote:

Originally posted by khtan

declare @table table
(
    Mark int
)

insert into @table
select 	10 union all
select	20 union all
select	30

select	(select count(*) from @table x where x.Mark <= t.Mark) as row_no,
	Mark
from	@table t

Not recommended to run on a table that has millions of rows

mageshks, Where do you want to show data?
If you use front end application do the 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