Hello, I lost my query that I used before :-( (I put the file in GIT after my second query)
I will however have another try and I will post it to you. I was also wondering if my indices were correctly defined. Many thanks for your answers, I appreciate a lot your help. Cheers, Sylvain On Mon, Feb 16, 2009 at 8:19 AM, Edzard Pasma <edz...@volcanomail.com>wrote: > 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 <sylvain.point...@gmail.com> > To: edz...@volcanomail.com, General Discussion of SQLite Database < > sqlite-users@sqlite.org> > 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 <edz...@volcanomail.com > >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 <sylvain.point...@gmail.com> > > 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
