October 21, 2010 at 12:07 pm
How can i select only half of the records from a query, i would pull 1st half of the names and secon half names seperately.
select name from master.sys.databases
October 21, 2010 at 12:10 pm
i tried "top 50 percent" but i does not work with distinct and also for bottom 50%
October 21, 2010 at 12:15 pm
Tara-1044200 (10/21/2010)
i tried "top 50 percent" but i does not work with distinct and also for bottom 50%
on my db with 51 databases, this gives me 26 rows: what does yours give?
select top 50 PERCENT name from master.sys.databases
and the rest of them(25 rows):
select name from master.sys.databases
WHERE NAME NOT IN(
select top 50 PERCENT name from master.sys.databases)
Lowell
October 21, 2010 at 12:17 pm
how would you get the rest of the databases?
October 21, 2010 at 12:22 pm
ok here is the thing..
I want ot use this query for my backup jobs so both of these queries would go into 2 diffferent jobs and like your example if there are 5 databses to be backed up and per the criteria, one job should run 3 and other job should run only 2.
October 21, 2010 at 12:26 pm
Tara-1044200 (10/21/2010)
how would you get the rest of the databases?
i updated my post with a second sql, probably while you were reading the first version of the post...so the second half is there now.
Lowell
October 21, 2010 at 12:35 pm
any idea how to achieve this?
November 11, 2010 at 2:00 pm
How can i make sure that my backup job(starts @ 7pm) should run only during nights on a schdueled day and if it takes more than 9 am the job should stop at that point (wihtout losing existing bak file as i have to use INIT=1 for bkps) and restart the next day to finish backps.
Is this possible, please shed your ideas, thanks.
November 11, 2010 at 5:46 pm
NTILE() is great for these kind of queries
eg:
select a.name
from (
select name,ntile(2) over (order by name) as split
from master.sys.databases
) as a
where a.split = 1
select a.name
from (
select name,ntile(2) over (order by name) as split
from master.sys.databases
) as a
where a.split = 2
November 12, 2010 at 12:27 pm
am i doing something worng here.
select distinct name from master.sys.databases s
inner join empdb.dbo.ldt a on s.name=a.dbname
where is_read_only = 0
and dbname in(select dbname,ntile(4) over (order by planid) as split from empdb.dbo.ldt
where tdate> getdate() - 30 )and split=2
order by name
November 17, 2010 at 5:59 pm
Tara-1044200 (11/12/2010)
am i doing something worng here.select distinct name from master.sys.databases s
inner join empdb.dbo.ldt a on s.name=a.dbname
where is_read_only = 0
and dbname in(select dbname,ntile(4) over (order by planid) as split from empdb.dbo.ldt
where tdate> getdate() - 30 )and split=2
order by name
You cannot actually reference something in an "IN" statement. You have a reference to "Split".
If you make the IN statement an INNER JOIN you can then reference it.
eg.
select distinct s.name from master.sys.databases s
inner join empdb.dbo.ldt a
on s.name=a.dbname
inner join (select dbname,ntile(4) over (order by planid) as split from empdb.dbo.ldt
where tdate> getdate() - 30 ) as b
on a.dbname = b.dbname
where is_read_only = 0
and b.split=2
order by s.name
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply