Wie die häufigsten Einträge ermitteln. Mehrere bei gleicher Anzahl?

[Klaus1]

Hallo,

ich beiße mir gerade die Zähne daran aus, wie ich über MySQL den häufigsten Eintrag selektieren kann, allerdings mehrere, wenn die Anzahl gleich ist.

Konkret: Über meine bishere Abfrage erhalte ich alle Einträge gezählt und absteigend sortiert. Grob vereinfacht sieht die Abfrage so aus: select count(name) as anz, name from tabelle group by name order by anz DESC;

Beispiel 1:

6   Anton
6   Hubert
2   Herbert

Beispiel 2:

4   Carsten
4   Detlef
4   Uwe
4   Tim

Wie bekomme ich jetzt aber im Beispiel 1 nur die ersten beiden und im Beispiel 2 alle 4 (weil ja alle 4 gleich häufig)?

Momentan habe ich nur die Idee, die größte Anzahl zu ermitteln (entweder über limit oder über max()) und dann den Select nochmal aufzurufen, nur diesmal mit der Where-Bedingung, das anz = dem Max-Wert sein muss.

Gibt es nicht doch eine Möglichkeit, das direkt in einer Abfrage zu ermitteln?

LG Klaus

[Rolf B]

Hallo Klaus1,

Gibt es nicht doch eine Möglichkeit, das direkt in einer Abfrage zu ermitteln?

Doch, natürlich. Aber letztlich führst Du auch dann die Abfrage mehrfach aus. Welches Datenbanksystem verwendest Du genau - incl. Version? Eventuell kann man hier mit CTEs arbeiten (Common Table Expression), um die Query übersichtlicher zu machen.

Mit "klassischem" SQL wird es etwas in dieser Art:

SELECT x.anz, x.name 
FROM   (SELECT   COUNT(name) AS anz, name 
        FROM     tabelle 
        GROUP BY name) x
WHERE  x.anz = (SELECT MAX(y.anzahl) 
                FROM   (SELECT   COUNT(name) AS anzahl
                        FROM     tabelle 
                        GROUP BY name) y) 

Es gibt eine Variante mit ORDER BY - aber die gilt eher als Hack. Im EXPLAIN hat sie bei mir allerdings eine Zeile weniger, d.h. ein Verarbeitungsschritt bei der Ausführung entfällt. Was in der Praxis schneller ist, muss man messen (und es braucht natürlich einen Index auf name, damit der COUNT ohne full table scan erfolgen kann).

SELECT x.anz, x.name 
FROM   (SELECT   COUNT(name) AS anz, name 
        FROM     tabelle
        GROUP BY name) x
WHERE  x.anz = (SELECT   COUNT(NAME) AS anzahl
                FROM     tabelle
                GROUP BY NAME
                ORDER BY anzahl DESC LIMIT 1)

Mit einer CTE sieht es so aus - setzt aber eine neuere Maria oder MYSQL 8 voraus. Und es macht die Arbeit für den Server nicht leichter, es spart Dir nur Schreibarbeit, denke ich. Der Explain wird bei durch die CTE nicht anders.

WITH namecount AS (
  SELECT   COUNT(name) AS anzahl, name 
  FROM     tabelle
  GROUP BY name
)
SELECT anzahl, name 
FROM   namecount 
WHERE  anzahl = (SELECT MAX(anzahl) FROM namecount)

Stell Dir die CTE in diesem Fall als eine Art temporäre View vor.

<exkurs>Richtig spannend werden CTEs erst, wenn sie rekursiv verwendet werden - aber dafür braucht man ein gut geöltes Gehirn, damit die beim Denken verknoteten Nerven sich nachher leichter aufribbeln lassen.</exkurs>

Rolf

[Klaus1]

Hallo Rolf,

vielen Dank für die Antwort. Leider ist es eine uralte MySQL-DB (5.6.11), aber lässt sich nicht aktualisieren, da die darauf laufende Anwendung sonst nicht mehr funktioniert. Das Ablösen der Anwendung steht auf der ToDo, aber so lange es noch läuft, sehen die Entscheider keinen Handlungsbedarf.

Die Abfrage ist leider etwas komplexer mit im Moment 4 verknüpften Tabellen und meine ersten Versuche mit klassischem SQL über Deinen Ansatz und auch mit "Having" waren nicht sehr erfolgreich.

Da die Tabellengröße dennoch überschaubar ist und es vermutlich keinen deutlich messbaren Unterschied zwischen zwei Abfragen hintereinander oder kombiniert geben wird, werde ich dann einfach zwei getrennte Abfragen verwenden.

Vielen Dank für Deine Hilfe.

LG Klaus

[Rolf B]

Hallo Klaus1,

meine ersten Versuche mit klassischem SQL über Deinen Ansatz (...) waren nicht sehr erfolgreich.

Wenn Du für die Anzahl-Ermittlung weitere Tabellen brauchst, wird es natürlich knifflig.

Ggf. kann Dir hier eine View weiterhelfen.

Aber letztlich ist es in zwei Queries auch nicht unbedingt langsamer, der Server cached die Ergebnisse. Du hast dann halt nur 2 Roundtrips zum Server, was nicht viel ausmacht, wenn SQL Server und Web Server auf der gleichen Dose laufen. Sind sie getrennt, wird's natürlich schon etwas langsamer, je nach Netzwerklatenzen beim Hoster.

Und es kann das Problem geben, dass es zwischen der ersten und zweiten Query ein Update auf die DB gibt und deine ermittelten Anzahlen dann nicht mehr stimmen. D.h. da muss eine Transaktion drumherum (was INNODB voraussetzt, mit MYISAM gibt's die nicht).

Ich bringe daher eine weitere Option ins Spiel: Handgemachtes Postprocessing.

SELECT COUNT(*) as Anzahl, Name
FROM tabelle
GROUP BY Name
ORDER BY Anzahl DESC

Das Ergebnis der Query liest Du ein und stoppst, wenn Du entweder keine Row mehr bekommst oder die Anzahl kleiner ist als die im vorigen Satz.

Rolf