[sqlite] having the Top N for each group
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
Re: [sqlite] having the Top N for each group
Hello, May be this is some idea: GROUP_CONCAT is a built-in aggregate function, that efficiently returns a list (as text) of items in each group. If you add ORDER By (before the group by) it also arranges the ordering. But it does not let you restrict the number of elements in each group, to only the top-N. I thought may be SUBSTR is a further solution. The use of LIMIT would be more elegant but I don't see how. is a TopN function planned for the future version of sqlite? ? 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
Re: [sqlite] sqlite3 cmd line: arrow
> Install libreadline5-dev before running configure. The readline > library is what provides the command line editing and recall. Thank you Roger - that fixed it. I've raised a minor ticket on the INSTALL document, suggesting it should mention this. David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
Hello! В сообщении от Saturday 14 February 2009 00:33:38 Nathan Biggs написал(а): > Is there a faster way to read an entire table other then: > > select * from table > > Not that is is slow, just curious. On linux you can do dd if=database.db of=/dev/null bs=1M and after perform "select ..." This trick does put your db to OS file cache and all read operations will be extremely fast. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Exception Problem - Heap
Hi All, I am getting an exception/error after some data is retrieved. I have copied here the screenshot... After storing 250 rows of data, it is giving exception... How to use malloc functions efficiently? Please help me anybody... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users