1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.

Most references

Discussion in 'SQL - Repository' started by adallas, May 26, 2009.

  1. adallas New Member

    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.

Share This Page