Create Numeric Sequence ID

Can this be written without tmp tables? (tmp tables simulate real tables)
Needed: an extra column indicating the correct sequence based on the order by
condition of databasename,appname.
create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
varchar(200),active bit null,id int identity(1,1) not null)
insert into #tmp1(appname,databasename,comment,active) Select
'EDIBU','Archived','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ASNTransfer','ASND','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'atcentral.exe','ATCentral','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'AtCentral.exe','OrderEntry','x','1'
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
insert into #tmp1(appname,databasename,comment,active) Select
'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
create table #tmp (idx int identity(1,1),appname varchar(50) null,
databasename varchar(50) null,comment varchar(200) null,active bit null,id
int null)
insert into #tmp(appname,databasename,comment,active,id)
select * from #tmp1 order by databasename,appname
select * from #tmp
drop table #tmp1
drop table #tmp
Regards,
Jamie
Sure...
SELECT
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id,
count(*)
FROM #tmp1 t1
JOIN #tmp1 t2 ON
t2.databasename <= t1.databasename
and
(t2.databasename < t1.databasename
or t2.appname <= t1.appname)
GROUP BY
t1.appname,
t1.databasename,
t1.comment,
t1.active,
t1.id
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:32FD08CE-A2A8-4BE3-93D0-0F9D740536DA@.microsoft.com...
> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order
> by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename
> varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie
|||Which version of SS are you using?
select
appname, databasename, comment, active,
(
select count(*)
from dbo.t1 as b
where b.appname < a.appname
or (b.appname = a.appname and b.databasename <= a.databasename)
) as rn
from
dbo.t1 as a
order by
rn
-- 2005
select
appname, databasename, comment, active,
row_number() over(order by appname, databasename) as rn
from
dbo.t1
order by
rn
go
AMB
"thejamie" wrote:

> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie
|||In 2005 can do this: (looking for a 2000 solution still)
select
b.rownum,a.id, a.databasename, a.appname
from
#tmp1a
inner join
(
SELECT ROW_NUMBER () OVER (ORDER BY databasename,appname) AS rowNum, ID
FROM #tmp1
) as b
on a.[id] = b.[id]
order by b.rownum
Regards,
Jamie
"thejamie" wrote:

> Can this be written without tmp tables? (tmp tables simulate real tables)
> Needed: an extra column indicating the correct sequence based on the order by
> condition of databasename,appname.
> create table #tmp1(appname varchar(50) null,databasename varchar(50),comment
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,active) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.insTDemand','ATSystemProcessing','x','1 '
> insert into #tmp1(appname,databasename,comment,active) Select
> 'ATOMS.dbo.spConvertFSCOs','EDID','x','1'
> create table #tmp (idx int identity(1,1),appname varchar(50) null,
> databasename varchar(50) null,comment varchar(200) null,active bit null,id
> int null)
> insert into #tmp(appname,databasename,comment,active,id)
> select * from #tmp1 order by databasename,appname
> select * from #tmp
> drop table #tmp1
> drop table #tmp
> --
> Regards,
> Jamie
|||Alejandro,
Just a minor correction... (looking for databasename,appname order rather
than the other way around)
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
and it looks like the one below works too but with only 172 records in my
actual database, there is no way to be sure at this point.
select
appname, databasename, comment, active,
(
select count(*)
from migrationdata as b
where b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
migrationdata as a
order by
rn
Regards,
Jamie
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Which version of SS are you using?
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from dbo.t1 as b
> where b.appname < a.appname
> or (b.appname = a.appname and b.databasename <= a.databasename)
> ) as rn
> from
> dbo.t1 as a
> order by
> rn
> -- 2005
> select
> appname, databasename, comment, active,
> row_number() over(order by appname, databasename) as rn
> from
> dbo.t1
> order by
> rn
> go
>
> AMB
> "thejamie" wrote:
|||Hi thejamie,

> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
You got it.

> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
It could be working because of the data you have right now, but that is not
the way to proceed when you need a tie breaker.
Example:
declare @.t table (
databasename varchar(50),
appname varchar(50)
)
insert into @.t values('db1', 'app1')
insert into @.t values('db1', 'app2')
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename = a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
-- wrong result
select
appname, databasename,
(
select
count(*)
from
@.t as b
where
b.databasename < a.databasename
or ( b.databasename < a.databasename and b.appname <= a.appname)
) as rn
from
@.t as a
order by
rn
go
AMB
"thejamie" wrote:
[vbcol=seagreen]
> Alejandro,
> Just a minor correction... (looking for databasename,appname order rather
> than the other way around)
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
> and it looks like the one below works too but with only 172 records in my
> actual database, there is no way to be sure at this point.
> select
> appname, databasename, comment, active,
> (
> select count(*)
> from migrationdata as b
> where b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> migrationdata as a
> order by
> rn
>
> --
> Regards,
> Jamie
>
> "Alejandro Mesa" wrote:
|||Toward a better understanding of traditional ranking queries:
http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html
|||Thanks Steve.
Regards,
Jamie
"Steve Dassin" wrote:

> Toward a better understanding of traditional ranking queries:
> http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html
>
>
|||Yep, missed the tie breaker... thanks
Regards,
Jamie
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Hi thejamie,
>
> You got it.
>
> It could be working because of the data you have right now, but that is not
> the way to proceed when you need a tie breaker.
> Example:
> declare @.t table (
> databasename varchar(50),
> appname varchar(50)
> )
> insert into @.t values('db1', 'app1')
> insert into @.t values('db1', 'app2')
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename = a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> -- wrong result
> select
> appname, databasename,
> (
> select
> count(*)
> from
> @.t as b
> where
> b.databasename < a.databasename
> or ( b.databasename < a.databasename and b.appname <= a.appname)
> ) as rn
> from
> @.t as a
> order by
> rn
> go
>
> AMB
>
> "thejamie" wrote: