Most references - SQL Recipes a FREE cookbook of SQL queries and examples

adallas · #1 (**permalink**) 05-26-2009, 04:56 AM

I came to SQL Recipes looking for this answer; didn't find it here, but I thought I'd come back to leave the answer I eventually figured out.

Records in TableB refer to specific records in TableA, many to one. You want to know the TableA ids that have the most references in TableB to create a top 10 list.

TableA - id is primary key
TableB - fk is foreign key to TableA; not unique

Code:

SELECT `fk` FROM `TableB` 
    GROUP BY `fk` 
    ORDER BY COUNT(`fk`) DESC 
    LIMIT 10;

This will produce a list of 10 TableA.id's, ordered by the number of times the id appears in TableB.fk. I don't know if it works in all dialects, but it works for me in MySQL 5.1.

Hope it helps.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

User Name		Remember Me?
Password