MySQL - Using COUNT(*) in the WHERE clause

I am trying to accomplish the following in MySQL (see pseudo code)

SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) > 10
ORDER BY lastupdated DESC

Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources.

Solution 1:

try this;

select gid
from `gd`
group by gid 
having count(*) > 10
order by lastupdated desc

I'm not sure about what you're trying to do... maybe something like

SELECT gid, COUNT(*) AS num FROM gd GROUP BY gid HAVING num > 10 ORDER BY lastupdated DESC

SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;

EDIT (if you just want the gids):

SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC

Just academic version without having clause:

select *
from (
   select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount > 10;