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 <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
