Vor einiger Zeit kam eine interessante Frage in der Newsgroup auf. Gesucht wurde der am häufigsten vorkommende Preis pro Artikelgruppe. Also der Modus je Artikelgruppe. Gegeben ist das folgende Ausgangsszenario:

CREATE TABLE #t (ArtikelGruppe CHAR(2), Preis DECIMAL(8,2))
INSERT INTO #t VALUES('SP', 1.2)
INSERT INTO #t VALUES('SP', 1.2)
INSERT INTO #t VALUES('SP', 2.1)
INSERT INTO #t VALUES('GR', 2.2)
INSERT INTO #t VALUES('GR', 2.3)
INSERT INTO #t VALUES('GR', 2.3)
INSERT INTO #t VALUES('GR', 2.3)

Gemäß der Aufgabenstellung soll nun

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

als Ergebnis erscheinen. Doch diese an und für sich scheinbar einfache Frage kann sich jedoch zu einem mittelschweren Brainteaser entwickeln. Den am häufigsten vorkommenden Preis über sämtliche Artikel kann man noch relativ leicht und intuitiv bestimmen mittels:

SELECT TOP 1 WITH TIES ArtikelGruppe, Preis, COUNT(*) Anzahl
  FROM #t
 GROUP BY ArtikelGruppe, Preis
  ORDER BY Anzahl DESC

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
GR            2.30       3

(1 row(s) affected)

Doch wie kommt nun die Gruppierung nach Artikelgruppen hier ins Spiel? Christoph Muthmann kam zu folgendem Lösungsansatz:

--Christoph's Ansatz
SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t a
 GROUP BY artikelgruppe, preis
  HAVING COUNT(*) >= 
   (SELECT MAX(Anzahl) 
      FROM 
      (SELECT artikelgruppe, preis, COUNT(*) AS Anzahl
         FROM  #t b
        WHERE b.artikelgruppe = a.artikelgruppe
         GROUP BY artikelgruppe, preis) c)

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

während mein ursprünglicher Ansatz folgendermaßen aussah:

--Mein ursprünglicher Ansatz
SELECT ArtikelGruppe,Preis, COUNT(*) Anzahl
  FROM #t a 
 GROUP BY ArtikelGruppe,Preis 
  HAVING NOT EXISTS 
   (SELECT *
      FROM #t b 
     WHERE b.ArtikelGruppe = a.ArtikelGruppe 
     GROUP BY b.Preis
     HAVING COUNT(b.ArtikelGruppe) > COUNT(a.ArtikelGruppe)) 

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)     

Vergleicht man nun die Ausführungspläne wird man bei meinem Ansatz einen "Lazy Spool" entdecken. Für gewöhnlich ist dies ein schlechtes Omen und man sollte versuchen, die Abfrage so umzuformulieren, daß dieser logische Operator nicht mehr auftaucht. Da ich aber Christoph's Ansatz sowieso für eleganter halte, formuliere ich lieber diesen ein bißchen um, um einen deutlich schlankeren und damit wahrscheinlich effektiveren Ausführungsplan zu erhalten:

SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t t
 GROUP BY ArtikelGruppe, Preis
  HAVING COUNT(*) =
  (SELECT MAX(Anzahl)
     FROM
      (SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
         FROM #t
        GROUP BY ArtikelGruppe, Preis) x
         WHERE ArtikelGruppe = t.ArtikelGruppe)

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

Christoph hat dann die drei obigen Statements mal an einem repräsentativen Datenbestand ausprobiert. Die Tabelle hat 250.000 Zeilen und umgesetzt auf das Beispiel 8 Artikelgruppen mit 358 verschiedenen Preisen. Ohne Verwendung von Indexes kommen dabei folgende Laufzeiten heraus:

• Christoph's Ansatz: 5.390 Millisekunden
• Mein Ansatz: 19.033 Millisekunden
• Neuer Ansatz: 436 Millisekunden

Erstellt man jetzt einen Index auf (ArtikelGruppe, Preis) kommt es zu folgenden Laufzeiten:

• Christoph's Ansatz: 153 Millisekunden
• Mein Ansatz: 500 Millisekunden
• Neuer Ansatz: 110 Millisekunden

Beeindruckend kann man erkennen, wie stark manche Abfragen durch geeignete Indexes beschleunigt werden können.

Der Vollständigkeithalber kann man alternativ zu MAX() zwar auch diverse TOP Varianten einsetzen:

SELECT artikelgruppe, preis, COUNT(*) AS Anzahl
  FROM #t a
 GROUP BY artikelgruppe, preis
  HAVING COUNT(*) = 
   (SELECT TOP 1 COUNT(*) 
      FROM #t
     WHERE a.ArtikelGruppe = ArtikelGruppe
     GROUP BY ArtikelGruppe, Preis
     ORDER BY COUNT(*) DESC) 

SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t t
 GROUP BY ArtikelGruppe, Preis
  HAVING COUNT(*) =
  (SELECT TOP 1 Anzahl
     FROM
      (SELECT TOP 100 PERCENT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
         FROM #t
        GROUP BY ArtikelGruppe, Preis 
         ORDER BY Anzahl DESC) x
        WHERE ArtikelGruppe = t.ArtikelGruppe)

die ebenfalls das gewünschte Ergebnis bringen, aber auch keine entscheidende Verbesserung im Vergleich zu den beiden in der Newsgroup geposteten Statements bzgl. der Ausführung bringen.