Re: [sqlite] How to select data from 2 lines in one line?
2009/8/19 Mário Anselmo Scandelari Bussmann: > Both work for me, Kit solution is very fast, but I think John is right. In > my case, the tables have sequencial rowid. If I delete some row, then will > not work anymore. When you create an index of column data, John's solution will be fast too. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
Both work for me, Kit solution is very fast, but I think John is right. In my case, the tables have sequencial rowid. If I delete some row, then will not work anymore. On Wed, Aug 19, 2009 at 12:44 PM, John Machinwrote: > On 20/08/2009 12:57 AM, Kit wrote: > > Right form (tested): > > > > SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS > > previous_data,temp.preult AS previous_preult > >FROM petr4,petr4 AS temp > >WHERE petr4.rowid=temp.rowid+1; > > Don't you think that relying on (a) rowid being consecutive (b) rowid > order being identical to date order is just a little bit dodgy? What if > the table has been created by a bulk load, not necessarily in ascending > date order? What if some rows have been deleted? > ___ > 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] How to select data from 2 lines in one line?
2009/8/19 John Machin: > On 20/08/2009 12:57 AM, Kit wrote: >> Right form (tested): >> >> SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS >> previous_data,temp.preult AS previous_preult >>FROM petr4,petr4 AS temp >>WHERE petr4.rowid=temp.rowid+1; > > Don't you think that relying on (a) rowid being consecutive (b) rowid > order being identical to date order is just a little bit dodgy? What if > the table has been created by a bulk load, not necessarily in ascending > date order? What if some rows have been deleted? OK, you found more better solution. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
On 20/08/2009 12:57 AM, Kit wrote: > Right form (tested): > > SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS > previous_data,temp.preult AS previous_preult >FROM petr4,petr4 AS temp >WHERE petr4.rowid=temp.rowid+1; Don't you think that relying on (a) rowid being consecutive (b) rowid order being identical to date order is just a little bit dodgy? What if the table has been created by a bulk load, not necessarily in ascending date order? What if some rows have been deleted? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
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 30 > rows). Thank you all!!! > > > On Wed, Aug 19, 2009 at 11:42 AM, John Machinwrote: > >> 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
Re: [sqlite] How to select data from 2 lines in one line?
Bingo! This works very well (a little bit slow, since I have a 30 rows). Thank you all!!! On Wed, Aug 19, 2009 at 11:42 AM, John Machinwrote: > 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
Re: [sqlite] How to select data from 2 lines in one line?
Right form (tested): SELECT petr4.data AS data,petr4.preult AS preult,temp.data AS previous_data,temp.preult AS previous_preult FROM petr4,petr4 AS temp WHERE petr4.rowid=temp.rowid+1; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
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
Re: [sqlite] How to select data from 2 lines in one line?
2009/8/19 Mário Anselmo Scandelari Bussmann: > 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 > 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0 > 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0 > 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0 > 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0 > 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0 > 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0 > 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 > 2007-01-05|46.19|2007-01-04|47.65 > 2007-01-08|46.59|2007-01-05|46.19 > 2007-01-09|45.52|2007-01-08|46.59 > 2007-01-10|45.25|2007-01-09|45.52 > 2007-01-11|45.21|2007-01-10|45.25 > 2007-01-12|45.15|2007-01-11|45.21 > 2007-01-15|44.89|2007-01-12|45.15 SELECT data,preult,temp.data,temp.preult FROM petr4,petr4 AS temp WHERE petr4.rowid=temp.rowid-1; -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select data from 2 lines in one line?
Without looking at your select statement it's very hard to help. But general suggestion is insert your results into temporary table and then issue a select on that table joined with itself with condition like t.rowid = prev.rowid + 1. Pavel On Wed, Aug 19, 2009 at 10:10 AM, Mário Anselmo Scandelari Bussmannwrote: > 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 > 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0 > 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0 > 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0 > 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0 > 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0 > 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0 > 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 > 2007-01-05|46.19|2007-01-04|47.65 > 2007-01-08|46.59|2007-01-05|46.19 > 2007-01-09|45.52|2007-01-08|46.59 > 2007-01-10|45.25|2007-01-09|45.52 > 2007-01-11|45.21|2007-01-10|45.25 > 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)? > ___ > 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
[sqlite] How to select data from 2 lines in one line?
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 3|2007-01-04|48.5|48.75|47.4|47.65|505916186.0 4|2007-01-05|47.2|47.99|45.1|46.19|581485748.0 5|2007-01-08|46.5|47.14|45.8|46.59|452501627.0 6|2007-01-09|45.97|46.39|44.61|45.52|587958198.0 7|2007-01-10|44.51|45.26|44.3|45.25|470899428.0 8|2007-01-11|44.9|46.43|44.76|45.21|542684895.0 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 2007-01-05|46.19|2007-01-04|47.65 2007-01-08|46.59|2007-01-05|46.19 2007-01-09|45.52|2007-01-08|46.59 2007-01-10|45.25|2007-01-09|45.52 2007-01-11|45.21|2007-01-10|45.25 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)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users