Hello,
in table haupt I store information about group, singer, song, voice and
datestamp.
[CODE]
CREATE TABLE 'haupt' ('id' INTEGER PRIMARY KEY, 'group' INTEGER,
'singer' INTEGER, 'song' INTEGER,
'voice' INTEGER, 'datestamp' TEXT);
INSERT INTO "haupt" VALUES(1, 1, 1, 1, 11, '2009-08-01');
INSERT INTO "haupt" VALUES(2, 1, 1, 1, 14, '2009-08-02');
INSERT INTO "haupt" VALUES(3, 1, 2, 1, 11, '2009-08-04');
INSERT INTO "haupt" VALUES(4, 1, 2, 1, 14, '2009-08-03');
[/CODE]
Now I want to filter data with the max datestamp lower or equal
'2009-08-30 00:00:00' and criteria group = 1 and song = 1.
[CODE]
SELECT * FROM haupt as hpt
WHERE
hpt.datestamp = (SELECT MAX(hpt_sub.datestamp)
FROM haupt AS hpt_sub
WHERE hpt_sub.id = hpt.id AND hpt.group = 1
AND hpt.song = 1 AND hpt_sub.datestamp <= '2009-08-30 00:00:00')
GROUP BY hpt.singer;
[/CODE]
Result:
2|1|1|1|14|2009-08-02
4|1|2|1|14|2009-08-03
Why do I get as result a dataset with id = 4 instead of id = 3 because
of the lower datestamp?
Or any suggestions for improving the subquery?
Thanks in advance.
Leo
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
