I said its slow but I forget index. Now is as fast as a lightningbolt!! Thanks again!
On Wed, Aug 19, 2009 at 12:23 PM, Mário Anselmo Scandelari Bussmann < mario.bussm...@gmail.com> wrote: > Bingo! This works very well (a little bit slow, since I have a 300000 > rows). Thank you all!!! > > > On Wed, Aug 19, 2009 at 11:42 AM, John Machin <sjmac...@lexicon.net>wrote: > >> On 20/08/2009 12:10 AM, Mário Anselmo Scandelari Bussmann wrote: >> > I have a table like this: >> > >> > petr4 >> > ----------- >> > rowid|data|preabe|premax|premin|preult|voltot >> > 1|2007-01-02|50.0|50.45|49.76|50.45|256115409.0 >> > 2|2007-01-03|50.16|50.4|48.01|48.7|492591256.0 >> [snip] >> > 9|2007-01-12|45.3|45.61|44.8|45.15|478912234.0 >> > 10|2007-01-15|45.61|45.85|44.89|44.89|317073087.0 >> > >> > I need a select that returns data,preult,previous data and previous >> preult: >> > >> > 2007-01-03|48.7|2007-01-02|50.45 >> > 2007-01-04|47.65|2007-01-03|48.7 >> [snip] >> > 2007-01-12|45.15|2007-01-11|45.21 >> > 2007-01-15|44.89|2007-01-12|45.15 >> > >> > How can I do that using only sql (no python, c or perl, no cursor)? >> >> No Python? How cruel :-) >> >> This works but you'd better have an index on 'data', and it looks like >> at least O(N**2) OTTOMH: >> >> sqlite> create table x (data,preabe,premax,premin,preult,voltot); >> sqlite> insert into x values >> ('2007-01-02',50.0,50.45,49.76,50.45,256115409.0); >> /* etc etc*/ >> sqlite> select a.data, a.preult, b.data, b.preult from x a, x b >> ...> where b.data = (select max(c.data) from x c where c.data < a.data) >> ...> order by a.data; >> 2007-01-03|48.7|2007-01-02|50.45 >> 2007-01-04|47.65|2007-01-03|48.7 >> [snip] >> 2007-01-12|45.15|2007-01-11|45.21 >> 2007-01-15|44.89|2007-01-12|45.15 >> sqlite> >> >> _______________________________________________ >> 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