query like auto increase

[rotelmak]

I am novice in sql. In my test database I have table with this structure and data:

myfield1
401
580
633
785
801

Can I get with query this output result, without usig identity column:

myfield1 recordno
401 1
580 2
633 3
785 4
801 5

 

[SQLDenis]

Insert into temp table with identity or do a running
count

Ranking in SQL server 2000

http://sqlservercode.blogspot.com/2006/03/ranking-in-sql-server-2000.html

Ranking in SQL server 2005

http://sqlservercode.blogspot.com/2006/03/rownumber-ntile-rank-and-denserank.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

here is how you do a running count

create table BlahCount(mycolumn_NOTFIELD int)
insert BlahCount values(401)
insert BlahCount values(580)
insert BlahCount values(633)
insert BlahCount values(785)
insert BlahCount values(801)


select * ,(select count(*) from BlahCount b2 where b2.mycolumn_NOTFIELD <= b1.mycolumn_NOTFIELD) as RowNo
from BlahCount b1

but insert into temp table will be faster because running count does a select for EVERY SINGLE ROW in the table!!!!!

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[bborissov]

What version of SQL Server you use? There are several RANKing functions in SQ Server 2005.
Also how you will your data to be stored if you have this:

myfield1
401
785
580
801
633

?
There is no guarantee that the record you added last will be the last psychical record of the table.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

Borislav I use Sql 2000

 

[SQLDenis]

you need to add the order by to the query I showed you

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[rotelmak]

Thank you SQLDenis. It works fine