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

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

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

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

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

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

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

"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

[sqlite] SQLite3 in PEAR

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

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

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

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

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,

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

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

[sqlite] Check for existence of substring in table

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

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

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