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
