auto number in SQL query - 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

Transact-SQL (2000)

auto number in SQL query

Author

Topic

u2p_inst
Yak Posting Veteran

78 Posts

Posted - 2004-01-28 : 23:08:06

Dear All,

Is possible to give the column auto number to the SQL query like column auto number in a table,How?

oh

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-29 : 02:57:45

In principle, it's possible - if in the table
there is an unique combination of (several) column(s):

create table #t (n char(1), m char(1))
insert into #t
select 'a','e' union all
select 'w','h' union all
select 'w','f' union all
select 'd','u'

select (select count(*) from #t t where t.n+t.m <= #t.n+#t.m),
n, m
from #t
order by 1

drop table #t

ns_nataly
Starting Member

13 Posts

Posted - 2004-01-29 : 04:11:13

If you ready to use temp. tables – use this and you can have any amount of fields:

Create table #t( field1 type1,field2 type2, Autonumber int IDENTITY(1,1))

Insert into #t(field1,field2)
Select your_field1,your_field2 from your_table

Select * from #t order by Autonumber

Drop table #t

Natalia

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-29 : 05:09:34

Then SELECT INTO looks even better:

select n=identity(int,1,1), z.* into #t

from (select top 100 percent * from t order by 2, 1) z

select * from #t order by 1

drop table #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources