Wes Freeman wrote:
>
> You were using the outside table alias in the subquery:
>
> (...)
>
> might work a little more as expected.
>
>
I modified the code a get the following results. Unfortunetly only a little
more as expected:
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.datum <= '2009-08-03 00:00:00');
-case Ensemble 1, Stueck 1, '2009-08-03 00:00:00'
result:
1|1|1|1|11|2009-08-01 00:00:00
2|1|2|1|11|2009-08-01 00:00:00
5|2|1|1|11|2009-08-01 00:00:00
comment: fault, id 5 with wrong ensemble id
-case Ensemble 1, Stueck 1, '2009-08-05 00:00:00'
result:
3|1|1|1|20|2009-08-05 00:00:00
4|1|2|1|20|2009-08-05 00:00:00
6|2|1|1|20|2009-08-05 00:00:00
comment: fault, id 6 with wrong ensemble id
-case Ensemble 2, Stueck 1, '2009-08-03 00:00:00'
result:
7|2|1|1|14|2009-08-02 00:00:00
8|2|2|1|14|2009-08-02 00:00:00
comment: as expected
-case Ensemble 2, Stueck 1, '2009-08-05 00:00:00'
result:
3|1|1|1|20|2009-08-05 00:00:00
6|2|1|1|20|2009-08-05 00:00:00
8|2|2|1|14|2009-08-02 00:00:00
comment: fault, id 3 with wrong ensemble id
What's going wrong?
Leo
--
View this message in context:
http://www.nabble.com/Problems-with-max%28datestamp%29-in-subquery-tp24905950p24937251.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
