quick mysql question

BrianK · 09-28-2005, 04:20 PM

I have a table that contains an id and a reference to an id in another number. The id is primary.

I want to find the most frequently occuring reference id in the table and/or all references that exist more than, say, 100 times.

I've come this far:

select reference_id, COUNT(*) FROM order_products GROUP BY order_id limit 20;

but if I don't limit 20, I get lots and lots of rows. How do I only return those rows with more than, say, 100 hits? And is there a way to find the single largest?

Thanks

tekkieman · 09-29-2005, 11:30 AM

Ok, my SQL is getting a little rusty, but in your original query, the reason you are getting so many rows is because you are basically selecting all rows from the table. You need a sub select statement to limit the return row count.

I would have to test a few queries, but you should be able to use the MAX function to return the largest count from the COUNT sub selection.

Google for SQL MAX function for some examples.

EDIT: This is generally what you're looking for...

Code:

SELECT order_id, reference_id FROM order_products
GROUP BY reference_id
HAVING COUNT(*) > 1

BrianK · 09-29-2005, 03:07 PM

Quote:

Originally posted by tekkieman

Code:

SELECT order_id, reference_id FROM order_products GROUP BY reference_id HAVING COUNT(*) > 1

ahh... "HAVING" - that's the operator I didn't know about.

Thanks a ton!

tekkieman · 09-29-2005, 04:29 PM

No problem.