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
