I currently am writing a query which retrieves the max amount of rows for a junction table. I'll use the following example in SQL Fiddle:
MyTable
┌─────────┬─────────┐ │ a_field │ b_field │ ├─────────┼─────────┤ │ a │ 1 │ │ b │ 2 │ │ c │ 3 │ └─────────┴─────────┘
JunctionTable
┌─────────┬──────────────────┐ │ a_field │ irrelevant_field │ ├─────────┼──────────────────┤ │ a │ z │ │ a │ y │ │ a │ x │ │ b │ z │ │ b │ y │ │ b │ x │ │ c │ z │ └─────────┴──────────────────┘
Note that in the JunctionTable
, 'a'
and 'b'
are both the most frequent. I want my query to retrieve '1'
and '2'
, which are the corresponding values to 'a'
and 'b'
in MyTable
.
A naïve version of the query would rely on the fact that the ordering is done on the amount and then limits it on 1 to get the max value:
SELECT b_field FROM MyTable WHERE a_field IN ( SELECT a_field FROM JunctionTable GROUP BY a_field ORDER BY COUNT(a_field) DESC LIMIT 1 );
What I want to do, though, is retrieve both 'a'
and 'b'
without increasing the LIMIT keyword. To achieve this, I can do the following:
SELECT b_field
FROM MyTable
INNER JOIN (
SELECT a_field, COUNT(a_field) AS amount
FROM JunctionTable
GROUP BY a_field
) JC ON JC.a_field = MyTable.a_field
WHERE amount = (
SELECT MAX(amount)
FROM (
SELECT COUNT(a_field) AS amount
FROM JunctionTable
GROUP BY a_field
) JC2
);
However this screams bad practice to me. Are there any better solutions to this problem?