Select statement problem - 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)

Select statement problem

Author

Topic

jyothi_jayanth
Starting Member

7 Posts

Posted - 2001-12-07 : 05:43:50

Dear friends
I am facing some problem in select statement
How can i get record sequence number
for example my select is resulting likr this
select fld1,fld2 from table1
123 fjsfj
124 fdjfg
125 fdfgf

but i sholud get like this

1 123 fjsfj
2 124 fdjfg
3 125 fdfgf

Can u please give me sql-statement for this
thanks in advance

Jayanthu Babu gadde

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-07 : 05:49:45

select
seq = (select count(*) from table1 t where table1.fld1 <= t.fld1) ,
fld1 ,
fld2
from table1
order by fld1

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-07 : 06:28:20

>=, not <=

Then you can sit back and wait until Microsoft get their query optimizer to detect this form and make it use the mythical rank operator.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-07 : 06:47:33

oops.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-07 : 09:01:16

By way of illustration, I tried this on a rather wide 110000 row table. Returning just the clustered primary key (which for not-very-good reasons is char(10)) and the pk's rank took 78 minutes (1 run performed).
(The server is a 2×Pentium III-S 1.13GHz, 1GB RAM, RAID-5 on 4×10k rpm SCSI disks, probably fairly lightly loaded other than this query, running SQL Server 2000 Standard Edition sp 2.)
Adding a redundant non-clustered index on the pk can help a bit (fewer pages to scan): here, it reduced it to 30 minutes (average of 2 runs).

On the other hand, this took 5 seconds, most of it getting the select result from the server to the screen:


CREATE TABLE #RankTable (
  rank int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED  
  pk char(10) NOT NULL
)


INSERT INTO #RankTable
SELECT pk FROM MyTable ORDER BY pk


SELECT * FROM #RankTable


DROP TABLE #RankTable

Edited by - Arnold Fribble on 12/07/2001 09:02:15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources