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
