On 4/07/2009 9:01 AM, Simon Slavin wrote: > On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: > >> Suppose my 15 Dates are: >> >> 2009-03-03 >> 2008-11-05 >> 2008-07-10 >> ... >> ... >> 2007-07-23 >> >> >> Assuming this is a SORTED dataset in ascending order by Date, I >> would need >> to extract 40 records that start with the record at 2009-03-03, then >> 40 >> records starting with the record at 2008-11-05, and so-forth. >> >> Can I do this in one SQL statement and have it produce one Recordset >> of this >> result? Or would I have to run 15 different queries? > > If you have exactly 40 records for each day then you can do it with a > JOIN. Or put all the dates into a long string like > 'x2009-03-03x2008-11-05x ...' and use 'LIKE'. However, if you have > different numbers of entries for each day then I can't think of a way > to do it in one SELECT. > > Oh, hold on, you mean you have one record for each day, and you want > the records for those days and the 39 days after each of those days. > You could make an extremely long SELECT with lots of 'AND' clauses. > But I think you're going to have to do it in software.
There's a strong presumption that there are missing days i.e. there is not a row for each possible day, so you can't just do "where date between x and x-plus-40-days" ... How many years of data? 10? That's max 3653 dates. Using Python, you'd run a query to fetch *all* rows in date order. Build a dict mapping date to row index. Then for each of your interesting dates, use the dict to get rowindex, and your up-to-40 required rows are result_set[rowindex:rowindex+40] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
