On Mon, Aug 10, 2009 at 2:25 PM, Leo Freitag<leofrei...@netcologne.de> wrote:
> 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
>
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.
> 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
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
