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
views:
68answers:
3
A:
Because the outer query & inner query uses the same table (looks like it), can't the query be written this way?
SELECT ts.SiteId, COUNT(ts.SiteId) AS Count
FROM ts
WHERE ts.UniqueID <> 'xxx'
GROUP BY ts.SiteId ORDER BY Count DESC
shahkalpesh
2010-06-07 05:32:50
This does not solve the OP's problem. The OP wants to exclude all SiteId's that have any row with UniqueID=xxx. Your answer only filters out those rows, but leaves any others with the same SiteId.
VeeArr
2010-06-07 05:34:41
@VeeArr You said it!
Vasu
2010-06-07 05:36:54
shahkalpesh
2010-06-07 05:53:37
+2
A:
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
Eric Mickelsen
2010-06-07 05:33:49
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.
VeeArr
2010-06-07 05:40:10
Thanks a lot. I used this:SELECT ts.SiteId, COUNT(ts.SiteId) AS Count FROM ts GROUP BY ts.SiteIdHAVING MAX(CASE WHEN ts.uniqueid = 'xxx' THEN 1 ELSE 0 END) = 0ORDER BY Count DESC
Vasu
2010-06-07 05:50:36
A:
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
Irawan Soetomo
2010-06-07 05:47:47