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