Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query like auto increase

Status
Not open for further replies.

rotelmak

Programmer
Nov 29, 2004
31
MK
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
 
here is how you do a running count
Code:
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





 
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:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top