Eu acho que descobri. Muito obrigado a Alex Reece por ser uma caixa de ressonância e a esta resposta por fornecer uma estrutura para obter resultados estáveis. Eu coloquei minha resposta final e meu processo para chegar abaixo.
Resposta final
> SELECT lc1.id, lc1.label, mc.max_cnt
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
) lc1
INNER JOIN (
SELECT id, label, max(cnt) as max_cnt
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
) lc2
GROUP BY label COLLATE UTF8_GENERAL_CI
) mc
ON lc1.cnt = mc.max_cnt
AND lc1.label LIKE mc.label COLLATE UTF8_GENERAL_CI;
+----+--------+-----+
| id | label | cnt |
+----+--------+-----+
| 1 | foobar | 3 |
| 6 | blah | 3 |
| 7 | Stuff | 1 |
+----+--------+-----+
Você pode ver um SQLFiddle dele aqui: link
Processo para chegar a ele
Contagens de computação
O primeiro passo foi simplesmente calcular o número de vezes que cada etiqueta foi usada. Isso é bem direto:
> SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id;
+----+--------+-----+
| id | label | cnt |
+----+--------+-----+
| 1 | foobar | 3 |
| 2 | FooBar | 1 |
| 3 | fooBar | 0 |
| 4 | Foobar | 1 |
| 5 | BLAH | 1 |
| 6 | blah | 3 |
| 7 | Stuff | 1 |
+-------------+-----+
Obtendo contagens máximas
Em seguida, preciso classificar os rótulos por contagem decrescente e, em cada conjunto de rótulos que são escritos da mesma forma, mas possuem letras maiúsculas diferentes, escolha a parte superior:
> SELECT id, label, max(cnt)
FROM (subquery ORDER BY cnt) AS s
GROUP BY label COLLATE UTF8_GENERAL_CI;
Isso resulta em uma consulta que se parece com isso:
> SELECT id, label, max(cnt)
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
ORDER BY cnt DESC;
) AS s
GROUP BY label COLLATE UTF8_GENERAL_CI;
+----+--------+----------+
| id | label | max(cnt) |
+----+--------+----------+
| 1 | foobar | 3 |
| 6 | blah | 3 |
| 7 | Stuff | 1 |
+----+--------+----------+
Isso parece certo! E é quase ...
Por que isso não funciona
Acontece que o MySQL não garante que o GROUP BY realize uma classificação estável. É apenas por detalhes de implementação interna / de mudança que fazer um ORDER BY na subconsulta, seguido pelo GROUP BY na consulta externa, faz com que a linha com classificação superior ainda esteja no topo. E a instrução SELECT id, label, max(cnt) não garante que o ID e o rótulo que ela pega serão da mesma linha que o max (cnt). Por exemplo, apenas alterar ORDER BY na subconsulta resulta nas mesmas contagens retornadas, mas nos rótulos incorretos:
> SELECT id, label, max(cnt)
FROM (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
ORDER BY cnt ASC;
) AS s
GROUP BY label COLLATE UTF8_GENERAL_CI;
+----+--------+----------+
| id | label | max(cnt) |
+----+--------+----------+
| 1 | fooBar | 3 |
| 6 | BLAH | 3 |
| 7 | Stuff | 1 |
+----+--------+----------+
Então, precisamos de uma maneira de evitar isso.
Uma solução mais estável
Por sorte, eu encontrei essa outra resposta , que explica como fazer algo parecido com o que eu quero. O problema é que, nesse problema, o valor que eles querem no máximo (timestamp) já está na tabela. Enquanto neste problema, eu tenho que calcular a contagem.
Então, minha primeira tentativa foi criar uma tabela contendo todas as informações de que preciso e, em seguida, fazer referência a ela como na outra resposta:
CREATE TABLE LabelCounts (
'id' INT, 'label' VARCHAR(80), 'cnt' INT
);
INSERT INTO LabelCounts (
SELECT Labels.id, Labels.label, COUNT(*) AS cnt
FROM Labels
JOIN ItemsToLabels
ON Labels.id = ItemsToLabels.label_id
GROUP BY Labels.id
);
SELECT *
FROM LabelCounts lc1
INNER JOIN (
SELECT id, label, max(cnt) as max_cnt
FROM LabelCounts
GROUP BY label COLLATE UTF8_GENERAL_CI
) lc2
ON lc1.cnt = lc2.max_cnt
AND lc1.label LIKE lc2.label COLLATE UTF8_GENERAL_CI;
Isso faz o trabalho. Você pode ver que ele recalcula corretamente o rótulo correto, não importando o retorno da consulta interna, ao JOIN na contagem sendo igual à contagem máxima.
Infelizmente, eu não iria executar isso em um ambiente onde eu poderia criar tabelas adicionais. E se eu usei CREATE TEMPORARY TABLE , não posso referenciá-lo duas vezes como preciso nesta consulta. Então o passo final foi alinhar a criação da tabela LabelCounts. Você pode ver a consulta resultante no topo desta resposta.