defining row number - 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

defining row number

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-03 : 15:39:40

hi,

i have a simple table


 create table AB
 (te_xt varchar(10)
 ,te_xt2 varchar(20)
 )
 
 insert into AB
 select 'text9','text6' union all
 select 'text2','text4' union all
 select 'text3','text5' 
 
 select 
 te_xt
 ,te_xt2
 ,'' as rownumber
  from AB

in last select i would like to define row numbering; e.g.: 1st row, 2nd row, etc.

I don't want to use identity(1,1) in create table, nor i want to use any indexes, etc.

i'm using sql2000.

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 15:53:06

You'll need to use a tally table then or move the data into a temp table and use an identity column there.

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

Subscribe to my blog

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-05-03 : 15:56:34

thanks :)
but that's something i don't want to use.
isn't there any better or faster way to do it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-03 : 15:58:05

Which part don't you want to use? The tally table or the temp table? The tally table is a good choice in SQL 2000.

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

Subscribe to my blog

sakthi
Starting Member

2 Posts

Posted - 2010-05-04 : 02:38:01

select Row_Number()over(order by text) as row from dbo.AB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-04 : 02:38:51

sakthi, ROW_NUMBER function isn't available in SQL Server 2000.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 04:02:08

on what order you want them to be numbered?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-05-04 : 08:06:19

You don't want to use IDENTITY because... it works too well?
You don't want indexes because.. you want no speed from it at all?

Or.. your teacher said don't use them?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-04 : 08:42:03

The fastest and best way?
Say goodby SQL Server 2000 and then you can use row_number() in 2005 or better 2008.
I love it!

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-07 : 04:25:22

quote:
Originally posted by slimt_slimt

hi,

i have a simple table

create table AB
(te_xt varchar(10)
,te_xt2 varchar(20)
)

insert into AB
select 'text9','text6' union all
select 'text2','text4' union all
select 'text3','text5'

select
te_xt
,te_xt2
,'' as rownumber
from AB

in last select i would like to define row numbering; e.g.: 1st row, 2nd row, etc.

I don't want to use identity(1,1) in create table, nor i want to use any indexes, etc.

i'm using sql2000.

thank you

Do this numbering in the front end application if you use

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources