3
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ I see you have anonymized the query code pretty heavily; would you mind to use the actual object names? We can still review your code as is but it is better to include the real names for context. \$\endgroup\$ – Phrancis Nov 1 '16 at 22:43
  • \$\begingroup\$ @Phrancis I could, but the names actually won't make very much sense as they are in the first place in dutch and secondly not really self describing, such as JunctionTable and MyTable. However, I am currently creating a SQL Fiddle to simulate the situation. \$\endgroup\$ – engineercoding Nov 1 '16 at 22:45
  • 1
    \$\begingroup\$ @engineercoding Naming is, famously, one of the hardest things in programming. The names not being very descriptive is exactly the kind of thing potential answerers might offer advice on. Unless you have a specific preference not to post them on the internet, please do put them in. \$\endgroup\$ – Kaz Nov 1 '16 at 22:50
2
\$\begingroup\$

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;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.