Jump to content

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


gray8110

Recommended Posts

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

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.