Querying for distinct items where value appears more than X times.

[gray8110]

I have a fairly simple query that I use to generate a list for a navigation menu. I'd like to be able to limit the list to items that appear more than once.

 

My existing query:

SELECT DISTINCT category FROM posts ORDER BY category ASC

 

It seems like I'd be able to use WHERE COUNT(category) >1 but the query fails.

 

I'd also be OK with doing a query where it is ordered by COUNT(category) and then LIMITING the results... something like this:

SELECT DISTINCT category FROM posts ORDER BY COUNT(category) DESC THEN category ASC LIMIT 6

 

Any ideas?? Thanks

[btherl]

When you want to apply conditions to aggregated values (such as a count), you must use "having"

 

SELECT category FROM posts
GROUP BY category
HAVING count(*) > 1
ORDER BY category ASC

 

Try this out.  HAVING is applied after GROUP BY.  I'm not sure if it'll work with distinct, but group by is equivalent to distinct.