Please help optimize this query:
SELECT ts.SiteId, COUNT(ts.SiteId) AS Count
FROM ts
WHERE ts.SiteId not in
(SELECT ts.SiteId FROM ts WHERE ts.uniqueid = 'xxx')
GROUP BY ts.SiteId ORDER BY Count DESC
Add a comment
|
2 Answers
SELECT ts.SiteId, COUNT(ts.SiteId) AS Count,
MAX(CASE WHEN ts.uniqueid = 'xxx' THEN 1 ELSE 0 END) As XXXUniqueID
FROM ts
GROUP BY ts.SiteId
HAVING XXXUniqueID = 0
ORDER BY Count DESC
3 Comments
VeeArr
This is what I was thinking. Additionally, if you don't want the extra column to be returned, you can probably move the entire
MAX(...) expression into the HAVING clause instead.
Zero
Thanks a lot. I used this: SELECT ts.SiteId, COUNT(ts.SiteId) AS Count FROM ts GROUP BY ts.SiteId HAVING MAX(CASE WHEN ts.uniqueid = 'xxx' THEN 1 ELSE 0 END) = 0 ORDER BY Count DESC
Daniel
I like the answer, but I'd like to know conceptually why this would perform any better. Perhaps there's a difference in JOIN type?
In SQL 2005 CTE, it might look like this:
;
WITH
e AS
(
SELECT ts.SiteId FROM ts
EXCEPT
SELECT ts.SiteId FROM ts WHERE ts.uniqueid = 'xxx'
)
SELECT e.SiteId, COUNT(e.SiteId) AS Count
FROM e
GROUP BY e.SiteId ORDER BY Count DESC
