sql server2000 1

[tempo1]

Hi,
I'd like to see my table accompanied by an ordinal number in each row.
as follows:
1 myname
2 hisname
3 anothername
4
.
.
100 etc..
the code should be something like:

select ordinal(), name
from
table1

Is there a function to replace the "ordinal()" i wrote?
thanks.

 

[bborissov]

For SQL Server 2000

DECLARE @Temp TABLE (Ord Int IDENTITY(1,1), Name varchar(200))
INSERT INTO @Temp (Name)
select name
       from table1

SELECT * from @Temp

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

 

[GeertVerhoeven]

Borislav Borissov,

I subscribed to this thread since I was interested in the solution. In SQL 2005 you have the ROW_NUMBER function but this doesn't exists in SQL 2000.

Would you also create an extra table if you have a table with for example 10.000.000 rows in it? What if this table contains like 50 columns? Would you create a temp table with an identity field and the primary key (+index)?

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[bborissov]

Geert,
I didn't create a TEMP table, I declare a Table variable which is different and much faster.

I doubt that somebody needs ALL 10 000 000 and ALL 50 fields from the table at once, but if I need such a result set I will design the table that way to get all the results I want faster and easy.

If I can't do it in SQL Server end I can do that easily in my front-end, after the whole result set is received.



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

 

[duchovnick]

Thanks a lot Borislav !