October 19, 2009 at 2:36 pm
I probably should figure out this by myself, but this is really driving me nut 'cuz I don't see any problem in my code and result is incorrect, here is my three queries and their results:
SELECT Count(1) as total, category FROM Metric m
inner JOIN Manager mn ON mn.[Login] = m.createuserid
WHERE m.status = 'Pending' and type = 'updateTargetAccountPassword'
group by category order by count(1) desc, mn.category desc
Result:
530Operations
25SharedServices
25Retail
17GlobalTransactionBanking
14Finance
9ScotiaLeasing
8HumanResources
7Legal
7ExecutiveOfficeAdmin
5ScotiaInsurance
5RealEstate
5GlobalRiskManagement
3Audit
Second query: to get the top 5 records
SELECT top 5 Count(1) as total, category FROM Metric m2
inner JOIN Manager mn2 ON mn2.[Login] = m2.createuserid
WHERE m2.status = 'Pending' and type = 'updateTargetAccountPassword'
group by mn2.category order by count(1) desc, mn2.category desc
Result:
530Operations
25SharedServices
25Retail
17GlobalTransactionBanking
14Finance
Third query: to get the rest records (basically those categories that are not in the second query's result)
select count(1) as total, mn1.category from Metric m1
inner JOIN Manager mn1 ON mn1.[Login] = m1.createuserid
where mn1.category not in (
SELECT top 5 category FROM Metric m2
inner JOIN Manager mn2 ON mn2.[Login] = m2.createuserid
WHERE m2.status = 'Pending' and type = 'updateTargetAccountPassword'
group by mn2.category order by count(1) desc, mn2.category desc
)
group by mn1.category order by count(1) desc, mn1.category desc
The result is INCORRECT (INACCURATE with light different number):
9ScotiaLeasing
9Legal
9HumanResources
8ExecutiveOfficeAdmin
5ScotiaInsurance
5RealEstate
5GlobalRiskManagement
4Audit
Can any one help me out? This has been driving me crazy for an hour.
Thank you in advance.
October 19, 2009 at 2:46 pm
Does this return the correct results?
select count(1) as total, mn1.category from Metric m1
inner JOIN Manager mn1 ON mn1.[Login] = m1.createuserid
where m1.status = 'Pending' and type = 'updateTargetAccountPassword'
mn1.category not in (
SELECT top 5 category FROM Metric m2
inner JOIN Manager mn2 ON mn2.[Login] = m2.createuserid
WHERE m2.status = 'Pending' and type = 'updateTargetAccountPassword'
group by mn2.category
order by count(1) desc, mn2.category desc
)
group by mn1.category
order by count(1) desc, mn1.category desc
October 19, 2009 at 9:30 pm
Couldn't test these, but the idea is to make one pass to take your totals and store them in a temp table that has a sequence number you can test.
SELECT Count(1) as total, category, IDENTITY(int,1,1) as rowSeq
INTO #TEMP
FROM Metric m
INNER JOIN Manager mn ON mn.[Login] = m.createuserid
WHERE m.status = 'Pending' and type = 'updateTargetAccountPassword'
GROUP BY category
ORDER by count(1) desc, category desc
select * from #temp where rowSeq <= 5 order by rowSeq
select * from #temp where rowSeq >5 order by rowSeq
There would be a subquery solution as well as this cte alternative. Same principal is to put entire result set into a temp table with a sequence column that can be tested to pick top 5 and then the rest.
;with cte1 as (SELECT Count(1) as total, category
FROM Metric m
INNER JOIN Manager mn ON mn.[Login] = m.createuserid
WHERE m.status = 'Pending' and type = 'updateTargetAccountPassword'
GROUP BY category )
,cte2 as (select total,category, ROW_NUMBER() over(order by total desc, category desc) as rowSeq from cte1)
select * into #temp from cte2
select * from #temp where rowSeq <= 5 order by rowSeq
select * from #temp where rowSeq >5 order by rowSeq
This should have the advantage of running the leftovers quicker, because you don't have to go through the primary tables a second time.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 19, 2009 at 9:54 pm
I think the only reason the 3rd query didn't return the correct results is because you forgot to include your original filter in the top-level query. I agree with Garadin's solution, but it needs an "AND " before "mn1.category not in (".
October 20, 2009 at 7:18 am
Oops, good catch Tom.
Also, Hal, I'd think about Bob's solution. The table may be small now and the performance difference minor, but as it grows, so would the difference between the solutions.
I would however consider removing the order by and the identity column if you go with his temp table and just use the counts to sort the temp table. Not only does this move the sort operation to a much smaller result set, but identity values are not guaranteed to be ordered when using identity in a select into. (Or at least they weren't in 2000, not sure if that is "fixed" in 2005 and on, any idea Bob?)
October 20, 2009 at 7:25 am
Hey Seth,
From somewhere I got the idea that by using an ORDER BY in the load of data to the temp table, I was in fact making sure the identity column would maintain the sequence. However, I can't cite you a source for that off the top of my head (Gail probably could.) I am researching the question now and will post whatever I find back here.
Worst case, I would fall back to a CTE solution, and use ROW_NUMBER() to generate the rowSeq column values, rather than IDENTITY
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 7:47 am
This was my source for it not being guaranteed:
http://support.microsoft.com/kb/273586
I actually just found that about a week ago so it was fresh on my mind.
October 20, 2009 at 8:05 am
I can't find any contrary authority to that, and I hadn't realized what parallelism would do to the IDENTITY function, so let's play it safe. Here is a revised query to use ROW_NUMBER() instead of IDENTITY.
;with cte as (SELECT Count(1) as total, category
FROM Metric m
INNER JOIN Manager mn ON mn.[Login] = m.createuserid
WHERE m.status = 'Pending' and type = 'updateTargetAccountPassword'
GROUP BY category )
select total,category, ROW_NUMBER() over(order by total desc, category desc) as rowSeq
into #temp
from cte
select * from #temp where rowSeq <= 5 order by rowSeq
select * from #temp where rowSeq >5 order by rowSeq
They really need a note about that in BOL under INSERT and under IDENTITY.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 11:44 am
Thank you all guys, it does help so much when you leave it for a while and come back and see it is fixed.
October 20, 2009 at 12:04 pm
You're welcome. This was a valuable learning experience for me.
Just in case anyone is curious. There is a follow-up discussion posted here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply