I don't think you want max() around collections.book_in_date. You want the max(collection_date) but the book_in_date from that row. Since the collection_date is unique, the book_in_date can only come from one record.
>-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Petite Abeille >Sent: Tuesday, 12 August, 2014 12:15 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] SQLite relative dates and join query help > > >On Aug 12, 2014, at 7:38 PM, Ben <sqlite_l...@menial.co.uk> wrote: > >> The result I'm after is: >> >> id, prod_code, creation_date, last_book_in_date, last_collection_date >> >> Where the final two columns are from the collection which is the >farthest in the future, but still within the 50-day period from creation. > >Perhaps something along these lines: > >select items.id, > items.prod_code, > items.creation_date, > max( collections.book_in_date ) as last_book_in_date, > max( collection_date ) as last_collection_date >from items > >join collections >on collections.id = items.collection_id > >where collections.book_in_date between items.creation_date and >items.creation_date + 50 >and collections.collection_date between items.creation_date and >items.creation_date + 50 > >group by items.id, > items.prod_code, > items.creation_date > >N.B. > >There is no 'date' type in SQLite. Will assume something else, say, a >Julian number for ease of date manipulation. > >> Should I instead be processing this in the application rather than >database? > >No. A database is the perfect place to process data. > >_______________________________________________ >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
