Problem bei subquery mit Max(Datum)

[leofreitag]

Hallo,

in Tabelle tblZO_Haupt sind Daten zu Ensemble, Stück, Sänger, Stimmzuordnung und Datum gespeichert.

CREATE TABLE 'tblZO_Haupt' ('id' INTEGER PRIMARY KEY, 'zo_tblEnsemble' INTEGER, 'zo_tblSaenger' INTEGER, 'zo_tblStueck' INTEGER,
 'zo_tblStimmbez' INTEGER, 'datum' TEXT);
INSERT INTO "tblZO_Haupt" VALUES(1, 1, 1, 1, 11, '2009-08-01');
INSERT INTO "tblZO_Haupt" VALUES(2, 1, 1, 1, 14, '2009-08-02');
INSERT INTO "tblZO_Haupt" VALUES(3, 1, 2, 1, 11, '2009-08-04');
INSERT INTO "tblZO_Haupt" VALUES(4, 1, 2, 1, 14, '2009-08-03');

Gesucht sind die Einträge mit dem jüngsten Datum kleiner als '2009-08-30' bei Kriterium zo_tblEnsemble = 1 und zo_tblStueck = 1

Select * FROM tblZO_Haupt as hpt 
Where 
hpt.datum = (SELECT MAX(hpt_sub.datum) 
FROM tblZO_Haupt AS hpt_sub 
WHERE hpt_sub.id = hpt.id AND hpt.zo_tblEnsemble = 1 
AND hpt.zo_tblStueck = 1 AND hpt_sub.datum <= '2009-08-30 00:00:00') 
GROUP BY hpt.zo_tblSaenger;

Ergebnis:

2|1|1|1|14|2009-08-02
4|1|2|1|14|2009-08-03

Frage:
Warum enthält das Abfrageergebnis einen Datensatz mit id = 4 und nicht wegen des jüngeren Datums den mit id = 3?

Ich arbeite übrigens mit SQLite version 3.3.14

Gruß
Leo

[frauwue]

Warum definierst Du das Datum als Text ?

[Falk Prüfer]

Hallo,

...Warum enthält das Abfrageergebnis einen Datensatz mit id = 4 und nicht wegen des jüngeren Datums den mit id = 3?

Weil deine Abfrage falsch ist!
Was hat das GROUP BY hpt.zo_tblSaenger in der äußeren Abfrage zu suchen? Du selektierst * und verwendest keine Aggregatfunktion, also wozu das GROUP BY?
Die innere Abfrage wird wg. hpt_sub.id = hpt.id auf immer nur EINEN Datensatz eingeschränckt - wozu brauchst du da das MAX()?

In Anbetracht des gewünschten Ergebnisses müßte die Abfrage wohl eher so aussehen:
[highlight=sql]
Select * FROM tblZO_Haupt as hpt
Where
hpt.datum = (
SELECT MAX(hpt_sub.datum)
FROM tblZO_Haupt AS hpt_sub
WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger
AND hpt.zo_tblEnsemble = 1
AND hpt.zo_tblStueck = 1
AND hpt_sub.datum <= '2009-08-30 00:00:00')
[/highlight]

Gruß Falk

[leofreitag]

Hallo Falk,

großartig, es funktioniert, und ich habe auch eine leichte Ahnung warum. Aber ich würde gerne mehr 'festen Boden' unter die Füße bekommen. Es gibt im Netz zwar unzählige Seiten mit SQL-Erläuterungen, aber systematisch im Sinne von 'vom Problem zur Lösung' kenne ich keins.
Für eine Empfehlung wäre ich dankbar.

Gruß
Leo

[leofreitag]

Hallo,

habe mich wohl doch etwas zu früh gefreut.

Hier ein Beispiel, bei dem ein Phänomen auftritt, das ich mir nicht erklären kann.

DROP TABLE 'tblZO_Haupt';
CREATE TABLE 'tblZO_Haupt' ('id' INTEGER PRIMARY KEY, 'zo_tblEnsemble' INTEGER, 'zo_tblSaenger' INTEGER, 'zo_tblStueck' INTEGER, 
'zo_tblStimmbez' INTEGER, 'datum' TEXT); 
INSERT INTO "tblZO_Haupt" VALUES(1,1,1,1,11,'2009-08-01 00:00:00');
INSERT INTO "tblZO_Haupt" VALUES(2,1,2,1,11,'2009-08-01 00:00:00');
INSERT INTO "tblZO_Haupt" VALUES(3,1,1,1,20,'2009-08-05 00:00:00');
INSERT INTO "tblZO_Haupt" VALUES(4,1,2,1,20,'2009-08-05 00:00:00');
INSERT INTO "tblZO_Haupt" VALUES(5,2,1,2,11,'2009-08-01 00:00:00');
INSERT INTO "tblZO_Haupt" VALUES(6,2,1,2,20,'2009-08-05 00:00:00');

-- Version 1
INSERT INTO "tblZO_Haupt" VALUES(7,2,1,2,14,'2009-08-02 00:00:00');
INSERT INTO "tblZO_Haupt" VALUES(8,2,2,2,14,'2009-08-02 00:00:00');


-- Version 2
-- INSERT INTO "tblZO_Haupt" VALUES(7,2,1,2,14,'2009-08-02 00:00:00');
-- INSERT INTO "tblZO_Haupt" VALUES(8,2,2,2,14,'2009-08-02 00:00:00');


SELECT * FROM tblZO_Haupt AS hpt 
WHERE hpt.datum = ( 
    SELECT MAX(hpt_sub.datum) 
    FROM tblZO_Haupt AS hpt_sub 
    -- WHERE hpt_sub.id = hpt.id 
    WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger
        AND hpt.zo_tblEnsemble = 1
        AND hpt.zo_tblStueck = 1 
        AND hpt_sub.datum <= '2009-08-02 00:00:00');

In Version 1 ist die Ergebnismenge leer.

In Version 2 (Datensätze 7 und 8 auskommentiert) erhalte ich ein Ergebnis:

1|1|1|1|11|2009-08-01 00:00:00
2|1|2|1|11|2009-08-01 00:00:00

Nur leider kann ich nicht nachvollziehen warum.
Weiß jemand Rat?

Gruß
Leo

[frauwue]

Zunächst musst Du Deine Abfrage noch berichtigen. Wenn Du nur Sätze mit Ensemble 1 und Stück 1 finden willst musst Du entweder Deine Subquery noch ergänzen um:

AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble
AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck

oder die äußere Where Bedingung um:

AND hpt.zo_tblEnsemble=1
AND hpt.zo_tblStueck =1

Erst dann lohnt es sich weiter drüber nachzudenken. Denn in Deinem jetzigen Beispiel werden Sätze gefunden mit dem Datum und Sänger aus dem Ergebnis der Subquery. Sie können aber durchaus andere Ensemble und Stücknummern haben.


Gruß

Gruß

[leofreitag]

Hallo,

danke für die Antwort. Ich habe sie wie folgt umgesetzt:

DROP TABLE 'tblZO_Haupt'; 
CREATE TABLE 'tblZO_Haupt' ('id' INTEGER PRIMARY KEY, 'zo_tblEnsemble' INTEGER, 'zo_tblSaenger' INTEGER, 'zo_tblStueck' INTEGER,  'zo_tblStimmbez' INTEGER, 'datum' TEXT); 
INSERT INTO "tblZO_Haupt" VALUES(1,1,1,1,11,'2009-08-01 00:00:00'); INSERT INTO "tblZO_Haupt" VALUES(2,1,2,1,11,'2009-08-01 00:00:00'); INSERT INTO "tblZO_Haupt" VALUES(3,1,1,1,20,'2009-08-05 00:00:00'); INSERT INTO "tblZO_Haupt" VALUES(4,1,2,1,20,'2009-08-05 00:00:00'); INSERT INTO "tblZO_Haupt" VALUES(5,2,1,1,11,'2009-08-01 00:00:00'); INSERT INTO "tblZO_Haupt" VALUES(6,2,1,1,20,'2009-08-05 00:00:00'); INSERT INTO "tblZO_Haupt" VALUES(7,2,1,1,14,'2009-08-02 00:00:00'); INSERT INTO "tblZO_Haupt" VALUES(8,2,2,1,14,'2009-08-02 00:00:00'); 

SELECT * FROM tblZO_Haupt AS hpt 
WHERE hpt.datum = ( 
    SELECT MAX(hpt_sub.datum) 
    FROM tblZO_Haupt AS hpt_sub 
    WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger 
        AND hpt_sub.zo_tblEnsemble = 1 
        AND hpt_sub.zo_tblStueck = 1 
        AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble          
        AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck         
        AND hpt_sub.datum <= '2009-08-03 00:00:00');

Ich habe damit einige Beispiele durchgespielt.
Hier die Ergebnisse:
-- bei Ensemble = 1, Stück = 1, 2009-08-03 ==> ok: id 1, 2
-- bei Ensemble = 1, Stück = 1, 2009-08-05 ==> ok: id 3, 4
-- bei Ensemble = 2, Stück = 1, 2009-08-03 ==> Fehler: nur id 5, anstatt 5, 7, 8
-- bei Ensemble = 2, Stück = 1, 2009-08-05 ==> Fehler: nur id 6, anstatt 6, 8

Leo

[frauwue]

Hallo, ich habe momentan nicht viel Zeit, um das gebau durchzudenken, aber die sauberste Lösung für Deine Query erscheint mit folgende:

SELECT * FROM tblZO_Haupt AS hpt
WHERE
hpt.zo_tblEnsemble = ....
AND hpt.zo_tblStueck = ...
AND hpt.datum = (
SELECT MAX(hpt_sub.datum)
FROM tblZO_Haupt AS hpt_sub
WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger
AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble
AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck
AND hpt_sub.datum <= '2009-08-03 00:00:00');

[leofreitag]

Hallo,

ich habe es mit den vier Fallbeispielen durchprobiert. Es funktioniert. Wau! Ich hab's nur noch nicht verstanden.
Was genau bewirken die drei Statements in der Unterabfrage: hpt_sub.xyz = hpt.xyz?

Wäre auch dankbar für einen Tutorial- oder Buchtipp für vergleichbare Phänomene.

Besten Dank!
Leo

[frauwue]

Hi Leo,

die Bedingungen in der Unterabfrage bewirken, dass Du Dir das maximale Datum nur aus der Menge der Datensätze heraussuchst, die in diesen Punkten mit dem Hauptselect übereinstimmen. Würdest Du beispielsweise hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger weglassen, so würde der Subselect das maximale Datum für alle Sänger suchen. Wenn jetzt dieses Datum nur bei einem Sänger vorkommt, so würdest Du nur diesen als Ergebnis erhalten usw...

Gruß