Search code examples
mysqlsqlsubquery

Ordering by a nested table count

I have two tables Major(majorId, label) and Minor(minorId, majorId).

I want return Major ordered by the count of Minor (and label).

Something like:

SELECT majorId,
       label
FROM Major ma
ORDER BY
  (SELECT COUNT(*)
   FROM Minor mi
   WHERE mi.majorId=ma.majorId), label;

The way I imagining it working is: The Minor table is grouped by majorId and then rows are counted for each majorId and then the Major table is sorted by that coresponding count and then by label.

Solution

  • How about this:

    SELECT majorId, label
FROM Major ma
ORDER BY (SELECT COUNT(*) FROM Minor mi WHERE mi.majorId=ma.majorId 
group by mi.majorId), label ;