On Mon, Jan 17, 2011 at 7:47 AM, David Burström <david.burst...@gmail.com>wrote:
> Hello all! > > I stumbled across this strange bug during Android development on 2.2.1 > late last night. Please run the following snippet in SQLite 3.7.2 and > 3.6.22 to compare the differences. The comments shows what alterations > you can make to make the query return the expected result. > > SELECT starttime, endtime from entry LEFT JOIN interval ON > interval.entryid = entry.id GROUP BY entry.id HAVING starttime = > MAX(starttime); > I don't think it makes sense in SQL (not just SQLite but SQL in general) for an aggregate query to return columns that are not in the GROUP BY clause. Certainly the values returned for those columns are undefined. So I'm not sure the query above makes any sense. If it worked for you before, your were just lucky. Perhaps something like this will work better for you: SELECT entry.id, max(starttime), (SELECT endtime FROM interval WHERE interval.entryid=entry.id) FROM entry LEFT JOIN interval ON interval.entryid=entry.id GROUP BY entry.id; > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
