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
