Re: [sqlite] How to select data from 2 lines in one line?

[Kit]

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?

[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.


On Wed, Aug 19, 2009 at 12:44 PM, John Machin  wrote:

> 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?

[Kit]

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?

[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?
___
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?

[Mário Anselmo Scandelari Bussmann]

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 Machin 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

Re: [sqlite] How to select data from 2 lines in one line?

[Mário Anselmo Scandelari Bussmann]

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 Machin  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

Re: [sqlite] How to select data from 2 lines in one line?

[Kit]

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?

[John Machin]

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?

[Kit]

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?

[Pavel Ivanov]

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
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
> 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?

[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

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