The recordset/table that holds all the current data could be 1000, 3000, 10,000, ??? records.
Total records fetched, however, would be just 15 x 40 = 600. You have 15 dates that mark the start of each 40 record segment. So plainly stated, with my 15 dates referred to as Date1 to Date15: 1. Search for Date1 in the database. 2. Starting from Date1, grab the next 39 records as well. Call this SET1. (only 1 column from each record, called POINTS, is needed). 3. Repeat 1 & 2 for Date2 to Date15, creating a SET2 to SET15. Therefore, I end up with 15 sets of 40 records. My display GRID has 15 rows and 40 columns. Each SET, 1 to 15, will occupy one row of this GRID, with POINTS inserted into the cells. 0 1 2 3 4 5 ... 39 (SET1) 0 1 2 3 4 5 ... 39 (SET2) 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 0 1 2 3 4 5 ... 39 (SET15) If possible, and if the best way to do this, it would be fine if all 15 sets of 40 were in one recordset. I could then simply run it through a loop and at the end of each 40 records increment the row variable. So I don't if it is better to create 15 recordsets (40 records each), or one recordset (600 records), or whether it is possible to create the 600 record to begin with. I hope this example/question is clear. :-^ Cheers! Rick -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin Sent: Friday, July 03, 2009 7:19 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL Query Question 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
