Re: [sqlite] having the Top N for each group

[Edzard Pasma]

Hello,

Your current solution is theoretically not optimal, as it evaluates a sub-query 
for each row in table T, whereas a construction with LIMIT does this only once 
for each group. If you wish I may look at the 'infinite' query, just mail it. 
Otherwise we at least have proved SQLite's incredible speed in doing UPDATE :)

Edzard Pasma  

--- sylvain.point...@gmail.com wrote:

From: Sylvain Pointeau 
To: edz...@volcanomail.com, General Discussion of SQLite Database 

Subject: Re: [sqlite] having the Top N for each group
Date: Sun, 15 Feb 2009 21:44:58 +0100

Hello,
on my large dataset, it tooks an infinite time.
I finished with :

update T
set ranknum = (select count(*) from T a where ... a.value >= T.value  )

and it works fast enough, in few minutes.

if you have better solution, I would be glad to change.

Cheers,
Sylvain

On Sun, Feb 15, 2009 at 10:06 AM, Edzard Pasma wrote:

> Hello again,
>
> The following solution is more elegant than my earlier group_cancat idea,
> and is just as fast. I had not expected that as it seems what you started
> with.
>
> select period.period, sales.product
> from period
> join sales on sales.rowid in (
>select rowid
>from sales
>where sales.period = period.period
>order by sales.qty desc
>limit 3);
>
> -- Edzard Pasma
>
>
> --- sylvain.point...@gmail.com wrote:
>
> From: Sylvain Pointeau 
> To: sqlite-users@sqlite.org
> Subject: [sqlite] having the Top N for each group
> Date: Sat, 14 Feb 2009 09:21:15 +0100
>
> Hello all,
> I am wondering if we have a method faster then the INNER JOIN which
> can be very slow in case of large number of rows, which is my case.
> I was thinking of a UDF that increment a number if the concatenation of the
> key column (or group columns) is the same, means:
> select col1, col2, udf_topN(col1||col2) from TTT order by value group by
> col1,col2
>
> will result into
>
> 1,1,1
> 1,1,2
> 1,1,3
> 2,1,1
> 2,1,2
> 2,1,3
> 4,3,1
> 4,3,2
> etc
>
>
> however I don't really find how to keep, initialize, and destroy a variable
> in a UDF for a query time execution
>
> do you have some idea?
> is a TopN function planned for the future version of sqlite?
>
> Many thanks,
> Sylvain
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] having the Top N for each group

[Sylvain Pointeau]

Hello,
on my large dataset, it tooks an infinite time.
I finished with :

update T
set ranknum = (select count(*) from T a where ... a.value >= T.value  )

and it works fast enough, in few minutes.

if you have better solution, I would be glad to change.

Cheers,
Sylvain

On Sun, Feb 15, 2009 at 10:06 AM, Edzard Pasma wrote:

> Hello again,
>
> The following solution is more elegant than my earlier group_cancat idea,
> and is just as fast. I had not expected that as it seems what you started
> with.
>
> select period.period, sales.product
> from period
> join sales on sales.rowid in (
>select rowid
>from sales
>where sales.period = period.period
>order by sales.qty desc
>limit 3);
>
> -- Edzard Pasma
>
>
> --- sylvain.point...@gmail.com wrote:
>
> From: Sylvain Pointeau 
> To: sqlite-users@sqlite.org
> Subject: [sqlite] having the Top N for each group
> Date: Sat, 14 Feb 2009 09:21:15 +0100
>
> Hello all,
> I am wondering if we have a method faster then the INNER JOIN which
> can be very slow in case of large number of rows, which is my case.
> I was thinking of a UDF that increment a number if the concatenation of the
> key column (or group columns) is the same, means:
> select col1, col2, udf_topN(col1||col2) from TTT order by value group by
> col1,col2
>
> will result into
>
> 1,1,1
> 1,1,2
> 1,1,3
> 2,1,1
> 2,1,2
> 2,1,3
> 4,3,1
> 4,3,2
> etc
>
>
> however I don't really find how to keep, initialize, and destroy a variable
> in a UDF for a query time execution
>
> do you have some idea?
> is a TopN function planned for the future version of sqlite?
>
> Many thanks,
> Sylvain
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[Ulrich Schöbel]

Igor Tandetnik wrote:
> "Ulrich Schöbel"
>  wrote in
> message news:4997ddb2.9070...@bmu.office-on-the.net
>> I have a very simple table 'friends' with only one column
>> 'link':
>>
>> create table friends (link text);
>>
>> Lets assume there are 2 rows, 'abc' and 'def'.
>>
>> Then there is a Tcl variable x containing a string. If $x
>> starts with either abc or def (if $x starts with any value
>> in the table) I want a TRUE value (or something
>> comparable) otherwise a FALSE.
> 
> select exists (select 1 from friends where ? like link || '%');
> 
> Igor Tandetnik 
> 

Wow! Nifty! Thanks Igor.

Regards

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[Igor Tandetnik]

"Ulrich Schöbel"
 wrote in
message news:4997ddb2.9070...@bmu.office-on-the.net
> I have a very simple table 'friends' with only one column
> 'link':
>
> create table friends (link text);
>
> Lets assume there are 2 rows, 'abc' and 'def'.
>
> Then there is a Tcl variable x containing a string. If $x
> starts with either abc or def (if $x starts with any value
> in the table) I want a TRUE value (or something
> comparable) otherwise a FALSE.

select exists (select 1 from friends where ? like link || '%');

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 in PEAR

[Nuno Magalhães]

Greetings,

I'm trying to build a simple PHP-based site with authentication. I
want to use PEAR::Auth and one of the DB-containers it provides.
PEAR::DB is apparently obsolete and recomends PEAR::MDB2, but i can't
check which version of sqlite does the latter support.

I'm using sqlite3, which containers/interfaces should i use? It *is* a
simple site, but i feel i oughta be using some standard way.

OT: does any of those packages support sessions?

TIA

Nuno Magalhães
LU#484677
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[John Machin]

On 15/02/2009 9:15 PM, Ulrich Schöbel wrote:
> John Machin wrote:

>> all I know about Tcl is that I don't want to 
>> know any more about Tcl :-)
> 
> You should want to ;-)

You should want to be using Python instead of Tcl ;-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[Ulrich Schöbel]

John Machin wrote:
> On 15/02/2009 8:17 PM, Ulrich Schöbel wrote:
>> Hi all,
>>
>> I'm stuck with my problem. Hopefully someone here can help.
>>
>> I have a very simple table 'friends' with only one column
>> 'link':
>>
>> create table friends (link text);
>>
>> Lets assume there are 2 rows, 'abc' and 'def'.
>>
>> Then there is a Tcl variable x containing a string. If $x
>> starts with either abc or def (if $x starts with any value
>> in the table) I want a TRUE value (or something
>> comparable) otherwise a FALSE.
> 
> You haven't said whether you problem is writing the SQL or the Tcl or 
> both ...

SQL

> here's some SQL that will return 1 if any such link value 
> exists, else 0. Are you sure you don't want to know which link value 
> matches?

Yes.

> What happens if more than 1 link value matches -- do you care?

No.

> 
> 
> sqlite> create table f (link text);
> sqlite> insert into f values('abc');
> sqlite> insert into f values('def');
> sqlite> select * from f;
> abc
> def
> sqlite> select exists(select 1 from f where link = substr('defend', 1, 
> length(link)));
> 1
> sqlite> select exists(select 1 from f where link = substr('abcpqr', 1, 
> length(link)));
> 1
> sqlite> select exists(select 1 from f where link = substr('xyzzy', 1, 
> length(link)));
> 0
> sqlite>
> 

Thanks a lot.

> You'll need to write the Tcl code to do that with your variable $x where 
>   I've got 'defend' etc ...

I'll do.

> all I know about Tcl is that I don't want to 
> know any more about Tcl :-)

You should want to ;-)

> 
> HTH,
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Thanks again

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check for existence of substring in table

[John Machin]

On 15/02/2009 8:17 PM, Ulrich Schöbel wrote:
> Hi all,
> 
> I'm stuck with my problem. Hopefully someone here can help.
> 
> I have a very simple table 'friends' with only one column
> 'link':
> 
> create table friends (link text);
> 
> Lets assume there are 2 rows, 'abc' and 'def'.
> 
> Then there is a Tcl variable x containing a string. If $x
> starts with either abc or def (if $x starts with any value
> in the table) I want a TRUE value (or something
> comparable) otherwise a FALSE.

You haven't said whether you problem is writing the SQL or the Tcl or 
both ... here's some SQL that will return 1 if any such link value 
exists, else 0. Are you sure you don't want to know which link value 
matches? What happens if more than 1 link value matches -- do you care?


sqlite> create table f (link text);
sqlite> insert into f values('abc');
sqlite> insert into f values('def');
sqlite> select * from f;
abc
def
sqlite> select exists(select 1 from f where link = substr('defend', 1, 
length(link)));
1
sqlite> select exists(select 1 from f where link = substr('abcpqr', 1, 
length(link)));
1
sqlite> select exists(select 1 from f where link = substr('xyzzy', 1, 
length(link)));
0
sqlite>

You'll need to write the Tcl code to do that with your variable $x where 
  I've got 'defend' etc ... all I know about Tcl is that I don't want to 
know any more about Tcl :-)

HTH,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Check for existence of substring in table

[Ulrich Schöbel]

Hi all,

I'm stuck with my problem. Hopefully someone here can help.

I have a very simple table 'friends' with only one column
'link':

create table friends (link text);

Lets assume there are 2 rows, 'abc' and 'def'.

Then there is a Tcl variable x containing a string. If $x
starts with either abc or def (if $x starts with any value
in the table) I want a TRUE value (or something
comparable) otherwise a FALSE.

How can I do that?

Thanks for your time and help.

Ulli
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] having the Top N for each group

[Edzard Pasma]

Hello again,

The following solution is more elegant than my earlier group_cancat idea, and 
is just as fast. I had not expected that as it seems what you started with.

select period.period, sales.product
from period 
join sales on sales.rowid in (
select rowid
from sales
where sales.period = period.period
order by sales.qty desc
limit 3);

-- Edzard Pasma


--- sylvain.point...@gmail.com wrote:

From: Sylvain Pointeau 
To: sqlite-users@sqlite.org
Subject: [sqlite] having the Top N for each group
Date: Sat, 14 Feb 2009 09:21:15 +0100

Hello all,
I am wondering if we have a method faster then the INNER JOIN which
can be very slow in case of large number of rows, which is my case.
I was thinking of a UDF that increment a number if the concatenation of the
key column (or group columns) is the same, means:
select col1, col2, udf_topN(col1||col2) from TTT order by value group by
col1,col2

will result into

1,1,1
1,1,2
1,1,3
2,1,1
2,1,2
2,1,3
4,3,1
4,3,2
etc


however I don't really find how to keep, initialize, and destroy a variable
in a UDF for a query time execution

do you have some idea?
is a TopN function planned for the future version of sqlite?

Many thanks,
Sylvain
___
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