Hallo,

ich muss gerade ein Statement schreiben, wo ich von allen verkauften Autos, immer genau die drei beliebtesten Baujahre brauche. Das folgende Statement würde mir alle zurückgeben, also müsste es noch irgendwie modifiziert werden.

Code sql:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SELECT     autoid,     baujahr,     COUNT(baujahr) AS summary FROM     (         SELECT 1 AS id, 1 AS autoid, 1972 AS baujahr         UNION SELECT 2 AS id, 1 AS autoid, 1973 AS baujahr         UNION SELECT 3 AS id, 1 AS autoid, 1973 AS baujahr         UNION SELECT 4 AS id, 1 AS autoid, 1973 AS baujahr         UNION SELECT 5 AS id, 1 AS autoid, 1974 AS baujahr         UNION SELECT 6 AS id, 1 AS autoid, 1974 AS baujahr         UNION SELECT 7 AS id, 1 AS autoid, 1974 AS baujahr         UNION SELECT 8 AS id, 1 AS autoid, 1974 AS baujahr         UNION SELECT 9 AS id, 1 AS autoid, 1976 AS baujahr         UNION SELECT 10 AS id, 2 AS autoid, 1974 AS baujahr         UNION SELECT 11 AS id, 2 AS autoid, 1974 AS baujahr         UNION SELECT 12 AS id, 2 AS autoid, 1974 AS baujahr         UNION SELECT 13 AS id, 2 AS autoid, 1977 AS baujahr         UNION SELECT 14 AS id, 2 AS autoid, 1977 AS baujahr         UNION SELECT 15 AS id, 2 AS autoid, 1978 AS baujahr     ) AS t1 GROUP BY     autoid,     baujahr

Gruß, Tommy

Ist ein wenig Tricki, aber mit Variablen durchaus lösbar.

Zuerst das ganze noch nach der autoid und dem summary sortieren. Dann jede Resultatzeile mit der Vorzeile vergleichen und hochzählen solange die autoid gleich bleibt. Wenn die sich ändert, den Zähler zurücksetzen. Am Schluss das ganze noch filtern

Code sql:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

SELECT     autoid,     baujahr,     summary FROM     (         SELECT             @counter := IF(autoid = @last_autoid, @counter+1, 1) AS counter,             -- Hier ist die Anzahl der auszugebenden Resultate. In deinem Beispiel 3             (@counter <= 3) AS flag,             @last_autoid := autoid AS autoid,             baujahr,             summary         FROM             -- Die Variablen initialisieren             (SELECT @last_autoid := 0, @counter :=0) AS vars,             (                 SELECT                     autoid,                     baujahr,                     COUNT(*) AS summary                 FROM                     (                         SELECT 1 AS id, 1 AS autoid, 1972 AS baujahr                         UNION SELECT 2 AS id, 1 AS autoid, 1973 AS baujahr                         UNION SELECT 3 AS id, 1 AS autoid, 1973 AS baujahr                         UNION SELECT 4 AS id, 1 AS autoid, 1973 AS baujahr                         UNION SELECT 5 AS id, 1 AS autoid, 1974 AS baujahr                         UNION SELECT 6 AS id, 1 AS autoid, 1974 AS baujahr                         UNION SELECT 7 AS id, 1 AS autoid, 1974 AS baujahr                         UNION SELECT 8 AS id, 1 AS autoid, 1974 AS baujahr                         UNION SELECT 9 AS id, 1 AS autoid, 1976 AS baujahr                         UNION SELECT 10 AS id, 2 AS autoid, 1974 AS baujahr                         UNION SELECT 11 AS id, 2 AS autoid, 1974 AS baujahr                         UNION SELECT 12 AS id, 2 AS autoid, 1974 AS baujahr                         UNION SELECT 13 AS id, 2 AS autoid, 1977 AS baujahr                         UNION SELECT 14 AS id, 2 AS autoid, 1977 AS baujahr                         UNION SELECT 15 AS id, 2 AS autoid, 1978 AS baujahr                     ) AS t1                 GROUP BY                     autoid,                     baujahr                 ORDER BY                     autoid,                     COUNT(*) DESC             ) AS myData     ) AS flagged_fata WHERE     flag = 1

Wooow. Danke, das werde ich gleich mal testen und umschreiben.

Achjam noch so als wichtige Info zum Umbau. Beim folgenden Teil ist die Reihenfolge wichtig. Ja nicht verdrehen

Code sql:

1 2 3 4

@counter := IF(autoid = @last_autoid, @counter+1, 1) AS counter,             -- Hier ist die Anzahl der auszugebenden Resultate. In deinem Beispiel 3             (@counter <= 3) AS flag,             @last_autoid := autoid AS autoid,

Naja, mindestesn muss der counter als erstes kommen. flag und autoid kannst du miteinander vertauschen