select max() geht nicht

[Carla]

Hallo

Ich habe ein Problem mit einer Access-DB-Abfrage. Kann mir bitte jemand helfen.

Hier meine Abfrage:
SQL_DATE = "select max(woche) as Woche, max(Jahr) as Jahr, ID, Ort from Handel where Ort = '" & RS_ID("Ort_Grob") & "' group by woche, jahr, ID, Ort"
set RS_Date = MyConn.Execute(SQL_DATE)

Diese Abfrage gibt mir einfach einen Wert zurück, aber nicht den max-Wert. Woche und Jahr sind als Zahl definiert. Kann mir bitte jemand sagen, was falsch ist?

Dank&Gruss
Carla

[luiggi]

Hi Carla,
probiere doch mal aus, das Problem einzugrenzen.
FAnge doch mal so an:
SQL_DATE = "select max(woche) as Woche
from Handel"
und dann baue immer mehr ein, dann siehst Du ja, wo das Problem liegt.
Ansonsten: ist das Absicht, dass Du Gross-/Kleinschreibung verwendest?
Sg Stefan

[Klaus Mock]

Hallo,

SQL_DATE = "select max(woche) as Woche, max(Jahr) as Jahr, ID, Ort from Handel where Ort = '" & RS_ID("Ort_Grob") & "' group by woche, jahr, ID, Ort"
set RS_Date = MyConn.Execute(SQL_DATE)

Diese Abfrage gibt mir einfach einen Wert zurück, aber nicht den max-Wert. Woche und Jahr sind als Zahl definiert. Kann mir bitte jemand sagen, was falsch ist?

Was verstehst Du unter 'einen Wert'?
Ausserdem ist es ziemlich 'ungewöhnlich' nach einem Wert zu gruppieren, von dem man den Maximalwert ermitteln will, da ja die Aggregatfunktionen, wie eben z.B. MAX(), bei einer verwendeten GROUP BY Klausel jeweils innerhalb einer Gruppierung arbeiten. D.h. MAX() ermittelt den jeweiligen Maximalwert je Gruppierung von Woche, Jahr, ID und Ort in Deinem Statement.

Grüße
  Klaus

[Axel Richter]

Hallo,

Hier meine Abfrage:
SQL_DATE = "select max(woche) as Woche, max(Jahr) as Jahr, ID, Ort from Handel where Ort = '" & RS_ID("Ort_Grob") & "' group by woche, jahr, ID, Ort"
set RS_Date = MyConn.Execute(SQL_DATE)

Diese Abfrage gibt mir einfach einen Wert zurück, aber nicht den max-Wert. Woche und Jahr sind als Zahl definiert. Kann mir bitte jemand sagen, was falsch ist?

Die Aggregatfunktionen MIN und MAX beziehen sich auf die Datensatzgruppe. Deine Gruppen bestehen jeweils aus Datensätzen, die in woche, jahr, ID, Ort übereinstimmen. Diese Gruppe hat deshalb zwangsläufig in jedem Datensatz den selben Wert für woche und Jahr. Du schränkst das Ganze nun mit WHERE auf einen bestimmten Ort ein. Eigentlich sollte RS_Date nun mehrere Datensätze enthalten, nicht nur einen, nämlich alle die, bei denen Ort = '" & RS_ID("Ort_Grob") & "' gilt, gruppiert nach woche, jahr, ID.

Du solltest also nicht nach woche und Jahr gruppieren, wenn Du das Maximum _unterschiedlicher_ Werte hierfür ermitteln willst. Und: Normalerweise ist die Gruppierung bei dieser Art der Abfrage vorrangig und Einschränkungen sollten danach per HAVING erfolgen.

Probiere:

SQL_DATE = "SELECT MAX(woche) as Woche, MAX(Jahr) as Jahr, ID, Ort FROM Handel GROUP BY ID, Ort HAVING Ort = '" & RS_ID("Ort_Grob") & "'"

viele Grüße

Axel

[Sven Rautenberg]

Moin!

Und: Normalerweise ist die Gruppierung bei dieser Art der Abfrage vorrangig und Einschränkungen sollten danach per HAVING erfolgen.

Nein, diese generelle Aussage kann ich nicht so stehen lassen.

Es ist auch von der Performance her ein Unterschied.

WHERE definiert, welche Datensätze aus der Tabelle als Grundlage für die Gruppierung überhaupt ausgewählt werden. HAVING definiert, welche fertig gruppierten Resultate in das Abfrageergebnis kommen.

HAVING wird üblicherweise genutzt, um nach der Gruppierung auf die Ergebnisse der Gruppierung zuzugreifen und danach zu filtern.

Es ist daher Unsinn in diesem konkreten Beispiel, das unverändert bleibende Datenfeld "Ort" aus WHERE in HAVING zu verlagern. Das führt nur dazu, dass außer dem gewählten Ort auch alle anderen Orte in die Gruppierungsphase einfließen, und am Ende mit dem HAVING wieder herausgefiltert werden - das bringt keine Ergebnisveränderung unter dem Strich, aber bei großen Tabellen erheblich mehr Rechenaufwand.

- Sven Rautenberg

[Axel Richter]

Hallo,

Es ist daher Unsinn in diesem konkreten Beispiel, das unverändert bleibende Datenfeld "Ort" aus WHERE in HAVING zu verlagern. Das führt nur dazu, dass außer dem gewählten Ort auch alle anderen Orte in die Gruppierungsphase einfließen,

Das tun sie, je nach internem Ablauf in der Datenbank, sowieso. Kennst Du Dich wirklich so gut mit den DB-Interna aus, dass Du das so behaupten kannst?

MS Access SQL bringt jedenfalls bei

SELECT id, Ort, Max(woche) AS MaxWoche, Max(Jahr) AS MaxJahr
FROM Tabelle2
WHERE Ort = "o1"
GROUP BY id;

die Fehlermeldung, dass nicht nach Ort gruppiert wird. Das ist ja auch richtig, da Ort als Feld direkt angesprochen wird und nicht per Aggregatfunktion. Es _könnte_ aber auch darauf hinweisen, dass trotz WHERE, welches ja _einen_ Ort vorgibt, eben immer _erst_ gruppiert wird. Wie gesagt, müsste man hier sehr tief in den DB-Interna stecken, um das endgültig bewerten zu können.

und am Ende mit dem HAVING wieder herausgefiltert werden - das bringt keine Ergebnisveränderung unter dem Strich, aber bei großen Tabellen erheblich mehr Rechenaufwand.

Ja? Meiner Meinung nach hängt das sehr von den Implementierungen der Query-Optimizer der einzelnen DBs ab, siehe oben.

viele Grüße

Axel

[Axel Richter]

Hallo,

Es ist daher Unsinn in diesem konkreten Beispiel, das unverändert bleibende Datenfeld "Ort" aus WHERE in HAVING zu verlagern. Das führt nur dazu, dass außer dem gewählten Ort auch alle anderen Orte in die Gruppierungsphase einfließen,

Korrektur:
Zumindest für MS SQL-Server 2005 stimmt, was Sven geschrieben hat.

Offensichtlich habe ich mich geirrt.

viele Grüße

Axel

[Sven Rautenberg]

Moin!

Es ist daher Unsinn in diesem konkreten Beispiel, das unverändert bleibende Datenfeld "Ort" aus WHERE in HAVING zu verlagern. Das führt nur dazu, dass außer dem gewählten Ort auch alle anderen Orte in die Gruppierungsphase einfließen,
Korrektur:
Zumindest für MS SQL-Server 2005 stimmt, was Sven geschrieben hat.

Offensichtlich habe ich mich geirrt.

Mein (eventuell etwas zu undeutlich umschriebener) Punkt war:

Man kann nicht einfach grundsätzlich die WHERE-Bedingung als HAVING schreiben. Wie gesagt: Im _Ergebnis_ dieses konkreten Querys ändert das nichts, andernfalls aber schon.

Nicht dass hinterher irgendwer deinen Tipp so interpretiert, dass WHERE und GROUP BY nicht miteinander funktionieren können, oder so.

- Sven Rautenberg

[Axel Richter]

Hallo,

Man kann nicht einfach grundsätzlich die WHERE-Bedingung als HAVING schreiben. Wie gesagt: Im _Ergebnis_ dieses konkreten Querys ändert das nichts, andernfalls aber schon.

Machen wir es doch ganz deutlich:
Man kann HAVING nur auf die Felder anwenden, die im Query direkt aufgerufen werden, oder auf Ergebnisse von Aggregatfunktionen. Nicht anwenden kann man HAVING auf Felder, die zwar in der Tabelle vorhanden sind aber im konkreten Query nicht vorkommen. HAVING behandelt nunmal die Resultatsdatensatzmenge _nach_ der Gruppierung und der Auswertung der Aggregatfunktionen.

Man muss also WHERE verwenden, wenn man nach Feldern filtern will, die nicht im aktuellen Query vorkommen. Man sollte WHERE (versuchen zu)[1] verwenden, um nach Feldern zu filtern, die nicht per Aggregatfunktion behandelt werden, deshalb also gar nicht erst in die Gruppierung müssen.

[1]ggf. mit DB-Analysetools die Performance vergleichen, um nicht doch in eine Aushebelung eventuell vorhandener Optimizer-Funktionen zu stolpern.

Nicht dass hinterher irgendwer deinen Tipp so interpretiert, dass WHERE und GROUP BY nicht miteinander funktionieren können, oder so.

Hm, das tut es manchmal auch nicht ;-). Genau nämlich dann, wenn man mit WHERE versucht nach dem Ergebnis von Aggregatfunktionen zu filtern, die ja erst nach Gruppierung und Ausführung der Aggregatfunktion zur Verfügung stehen. Dafür _muss_ man HAVING anwenden.

viele Grüße

Axel

[Sven Rautenberg]

Moin!

Es ist daher Unsinn in diesem konkreten Beispiel, das unverändert bleibende Datenfeld "Ort" aus WHERE in HAVING zu verlagern. Das führt nur dazu, dass außer dem gewählten Ort auch alle anderen Orte in die Gruppierungsphase einfließen,
Das tun sie, je nach internem Ablauf in der Datenbank, sowieso. Kennst Du Dich wirklich so gut mit den DB-Interna aus, dass Du das so behaupten kannst?

Die tatsächliche Implementierung eines Querys, nachdem alle Analyzer, Optimizer etc. drübergelaufen sind, und die Datenbank dann anfängt, ist für mich recht irrelevant. Der Querystring-Optimierungsprozess kann bei guten Querys noch ein wenig mehr Speed bringen, bei schlechten Querys die gröbsten Schnitzer ausbaden.

MS Access SQL bringt jedenfalls bei

SELECT id, Ort, Max(woche) AS MaxWoche, Max(Jahr) AS MaxJahr
FROM Tabelle2
WHERE Ort = "o1"
GROUP BY id;

die Fehlermeldung, dass nicht nach Ort gruppiert wird.

Das hat aber nichts mit WHERE oder HAVING zu tun. Auch mit der Datensatzauswahl in HAVING würde die Meldung kommen. Aus grundsätzlichen Syntaxüberlegungen heraus.

Das ist ja auch richtig, da Ort als Feld direkt angesprochen wird und nicht per Aggregatfunktion. Es _könnte_ aber auch darauf hinweisen, dass trotz WHERE, welches ja _einen_ Ort vorgibt, eben immer _erst_ gruppiert wird.

Ein Optimizer müßte dann ja erkennen, dass die WHERE-Klausel nur zu einem einzigen Ort führen kann, weshalb in diesem speziellen Fall dann die Aufführung des Ortes in SELECT erlaubt ist, obwohl nicht nach ihm gruppiert und auch keine Aggregatfunktion drauf angewendet wird.

Das könnte man als SQL-Schreiber dann noch simpler machen:
SELECT if, 'o1', max(woche) as MaxWoche, max(Jahr) as MaxJahr ...(Rest wie gehabt). Einfach den fixen String ins SELECT tun, und fertig.

Ich halte jedenfalls viel davon, in den Querys die grundsätzlichen Ideen und Vorgaben, die SQL mit sich bringt, einzuhalten. Es hat schließlich einen Grund, warum die Reihenfolge der diversen Kommandos im SELECT eine Rolle spielt.

- Sven Rautenberg

[Frank (no reg)]

Salut,

SELECT id, Ort, Max(woche) AS MaxWoche, Max(Jahr) AS MaxJahr
FROM Tabelle2
WHERE Ort = "o1"
GROUP BY id;

die Fehlermeldung, dass nicht nach Ort gruppiert wird. Das ist ja auch richtig, da Ort als Feld direkt angesprochen wird und nicht per Aggregatfunktion. Es _könnte_ aber auch darauf hinweisen, dass trotz WHERE, welches ja _einen_ Ort vorgibt, eben immer _erst_ gruppiert wird. Wie gesagt, müsste man hier sehr tief in den DB-Interna stecken, um das endgültig bewerten zu können.

Der erste Teil ist mehr falsch als richtig. Das Query hat "Ort" im Output und es wird nur nach Id gruppiert (nach dem surrogativen PK in derselben Tabelle zu gruppieren ist relativ schwachsinnig) und nicht nach Ort. Es beschwert sich also nicht über WHERE Ort = 'o1' sondern über SELECT id, Ort.

Ansonsten beinhaltet deine Meinung sehr viel "Konjunktivismus" (könnte, hätte, müsste).

Cheers,
Frank

[Vinzenz Mai]

Hallo Carla,

Hier meine Abfrage:
SQL_DATE = "select max(woche) as Woche, max(Jahr) as Jahr, ID, Ort from Handel where Ort = '" & RS_ID("Ort_Grob") & "' group by woche, jahr, ID, Ort"

Als erstes wirfst Du zum Debuggen Nicht-SQL-Code raus. D.h. teste Deine Anweisung im Abfrageeditor von Access. Da dieser meiner Erfahrung nach nur beschränkt zum Editieren von Abfragen tauglich ist, schreibe Deine Anweisung in den Texteditor Deiner Wahl und füge ihn ins SQL-Fenster von Access ein.

Diese Abfrage gibt mir einfach einen Wert zurück, aber nicht den max-Wert. Woche und Jahr sind als Zahl definiert. Kann mir bitte jemand sagen, was falsch ist?

Woche und Jahr als Zahl zu definieren, ist gar keine gute Idee. Die Kalenderwoche ergibt sich aus dem Datum. Soweit ich mich erinnere verfügt MS Access ähnlich wie MySQL über ein enorm umfangreiches Sortiment an Funktionen, die man in Abfragen einbauen kann. Vor allem läßt sich der Funktionsumfang über VBA wirklich bequem erweitern.

Hmm, schauen wir uns ein Beispiel an

ID woche Jahr Ort
--------------------
 1    17 2006 Hamburg
 7    50 2005 Hamburg
 3    23 2005 Berlin
 5     1 2006 Hamburg

Ich vermute, das von Dir gewünschte Ergebnis sieht für den Ort Hamburg wie folgt aus:

ID woche Jahr Ort
--------------------
 1    17 2006 Hamburg   -- höchste Woche im Jahr 2006
 7    50 2005 Hamburg   -- höchste Woche im Jahr 2005

Wäre dies so? Wenn ja, dann benötigst Du eine Unterabfrage. Die gute Nachricht dazu ist, dass Jet-SQL, der SQL-Dialekt von MS Access, Unterabfragen unterstützt. Wie das prinzipiell geht, kannst Du folgendem Archivposting entnehmen. Lass Dich nicht davon abschrecken, dass da MySQL steht, das geht unter MS Access ganz genauso.

Freundliche Grüße

Vinzenz