You were using the outside table alias in the subquery:
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-02 00:00:00');
might work a little more as expected.
Wes
On Wed, Aug 12, 2009 at 7:19 AM, Leo Freitag<leofrei...@netcologne.de> wrote:
>
>
> P Kishor-3 wrote:
>>
>> On Mon, Aug 10, 2009 at 2:25 PM, Leo Freitag<leofrei...@netcologne.de>
>> wrote:
>>
>> sqlite> SELECT * FROM haupt WHERE datestamp = (SELECT Max(datestamp)
>> FROM haupt WHERE "group" = 1 AND song = 1);
>> 3|1|2|1|11|2009-08-04
>> sqlite>
>>
>> Although, I would *seriously* advise you to reconsider naming a table
>> column as "group" as that is a reserved SQL keyword.
>>
>>
>
> Thanks,
> here is the version with proper key words and a new question.
>
> The aim of all this is:
> To filter data by singer with the max datestamp lower or equal a given date.
>
>
> [CODE]
> 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');
> [/CODE]
>
> In version #1 there are no result.
> In version #2 (if I commented out the two lines) there is a result:
> 1|1|1|1|11|2009-08-01 00:00:00
> 2|1|2|1|11|2009-08-01 00:00:00
>
> I don't understand what happens here.
> Any ideas?
> Leo
>
>
>
> --
> View this message in context:
> http://www.nabble.com/Problems-with-max%28datestamp%29-in-subquery-tp24905950p24934390.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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
