Selecting attributes corresponding to the most frequent entries in a junction table

Question

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?

Answer 1

Observe that this expression is almost repeated:

SELECT a_field, COUNT(a_field) AS amount
    FROM JunctionTable
    GROUP BY a_field

That is an indication that you could use a Common Table Expression. Given the limited information I have about your situation, I would call it JunctionCount. I would rename amount, which is ambiguous, to occurrences.

To obtain all junctions that have the maximal number of occurrences, you can use the RANK() window function.

WITH JunctionCount AS (
    SELECT a_field, COUNT(*) AS occurrences
        FROM JunctionTable
        GROUP BY a_field
), MostJunctions AS (
    SELECT a_field, RANK() OVER (ORDER BY occurrences DESC) AS occurrence_rank
        FROM JunctionCount
) SELECT b_field
    FROM MyTable
        INNER JOIN MostJunctions
            ON MyTable.a_field = MostJunctions.a_field
    WHERE MostJunctions.occurrence_rank = 1;