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:

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 b
y
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,activ
e) Select
'EDIBU','Archived','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'ASNTransfer','ASND','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'atcentral.exe','ATCentral','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'AtCentral.exe','OrderEntry','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) Select
'ATOMS.dbo. insTDemand','ATSystemProcessing','x','1'
insert into #tmp1(appname,databasename,comment,activ
e) 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,
JamieSure...
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,activ
e) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ATOMS.dbo. insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) 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),comme
nt
> varchar(200),active bit null,id int identity(1,1) not null)
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'EDIBU','Archived','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ASNTransfer','ASND','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'atcentral.exe','ATCentral','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'AtCentral.exe','OrderEntry','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) Select
> 'ATOMS.dbo. insTDemand','ATSystemProcessing','x','1'
> insert into #tmp1(appname,databasename,comment,activ
e) 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:
[url]http://beyondsql.blogspot.com/2007/06/dataphor-sql-visualizing-ranking-query.html[
/url]|||Thanks Steve.
--
Regards,
Jamie
"Steve Dassin" wrote:

> Toward a better understanding of traditional ranking queries:
> http://beyondsql.blogspot.com/2007/...ry.htm
l
>
>|||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 no
t
> 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:
>

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,
JamieSure...
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:
> 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|||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:
> 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:
> > 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|||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:
> 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:
> > 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:
> >
> > > 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

create DTS programmly

is it possible to create DTS programmly and is there a real file for each
DTS in my SQL SERVER?
Regards,
OfirDTS has a COM API so you can create DTS packages programatically using
VB for example. If you open an existing package in the Designer you can
select Package / Save As to save it as VB code that you can then modify
yourself if you like. You can also save a package as a Striuctured
Storage file (.DTS)
David Portas
SQL Server MVP
--|||and how do i run this vb file?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1117033002.025025.117300@.f14g2000cwb.googlegroups.com...
> DTS has a COM API so you can create DTS packages programatically using
> VB for example. If you open an existing package in the Designer you can
> select Package / Save As to save it as VB code that you can then modify
> yourself if you like. You can also save a package as a Striuctured
> Storage file (.DTS)
> --
> David Portas
> SQL Server MVP
> --
>|||You would first have to compile the file to an executable using
VisualBasic / VisualStudio - you said you wanted to do it
programmatically so I assumed you meant you wanted to create an
executable.
Maybe you were instead looking for a script to create DTS packages from
DOS / Windows / TSQL without compiling any code? I'm not sure that's
possible. Perhaps it's possible in VB script - I haven't tried that
myself.
What you can do is save the DTS file and then copy it to where you want
it and *execute* it from DOS using the DTSTRUN utility. Look up DTSRUN
in Books Online.
David Portas
SQL Server MVP
--|||Also checkout www.sqldts.com. It is an excellent site packed with all the
relevant information on DTS including its programming considerations.
Anith