gray8110 Posted January 17, 2009 Share Posted January 17, 2009 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 Quote Link to comment Share on other sites More sharing options...
btherl Posted January 17, 2009 Share Posted January 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.