Re: [sqlite] SQL Query Question
Thanks Igor! Excellent advice and example. It would appear that where my brain freezed is in the use of ">=". For the life of me, I could not think of what would instruct the DB to go beyond the fetch date. Really appreciate it! Cheers! Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, July 03, 2009 9:19 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL Query Question Rick Ratchford wrote: > For example, say I have 15 Dates already extracted by a previous > query. > > I need to now get the 40 records that start at each of those 15 Dates. > > 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? You would be better off with 15 separate queries, like this: select * from myTable where Date >= :anchorDate order by Date asc limit 40; If you insist, you can get everything with a single query - but it will be slow as molasses in winter: select * from dateList dl join myTable t on ( select count(*) from myTable t1 where t1.Date between dl.Date and t.Date ) <= 40 order by dl.Date, t.Date; where dateList is the table (perhaps temporary) containing your 15 dates. Igor Tandetnik ___ 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
Re: [sqlite] SQL Query Question
Rick Ratchford wrote: > For example, say I have 15 Dates already extracted by a previous > query. > > I need to now get the 40 records that start at each of those 15 Dates. > > 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? You would be better off with 15 separate queries, like this: select * from myTable where Date >= :anchorDate order by Date asc limit 40; If you insist, you can get everything with a single query - but it will be slow as molasses in winter: select * from dateList dl join myTable t on ( select count(*) from myTable t1 where t1.Date between dl.Date and t.Date ) <= 40 order by dl.Date, t.Date; where dateList is the table (perhaps temporary) containing your 15 dates. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Query Question
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
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
Re: [sqlite] SQL Query Question
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. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
