On Sun, Mar 14, 2010 at 06:25:31PM -0400, Shawn Green writes: SG> I know it's bad form to reply to yourself but I spotted something I SG> could clarify
Thank you very much, it works good enough in this case! SG> Shawn Green wrote: >> ... >> >> One way to do this is to materialize the results of the ORDER BY into a >> temporary table with an auto_increment column defined on it. Then just >> do a query against the temporary table with the HAVING condition as your >> new WHERE clause. >> >> CREATE TEMPORARY TABLE rankme ( >> rank int auto_increment >> , asn int >> , country varchar(15) >> , n24 float >> , PRIMARY KEY (rank) >> ) ENGINE = MEMORY; >> >> INSERT rankme (asn, country, n24) >> select asn, country, avg(n24) as n24 >> from asrank join asname using (asn) >> group by asn >> order by n24 desc; >> >> SELECT * >> from rankme >> where country='UA' >> ORDER BY n24 desc *** >> limit 10; >> >> DROP TEMPORARY TABLE rankme; >> >> *** NOTE: without the ORDER BY clause, you are not guaranteed to get >> your rows back in any particular order. As you want the top 10 listings >> sorted by n24 for the country 'UA', you still need the ORDER BY to make >> this a deterministic query. SG> You do not need to sort by n24 in this last query. In fact, since we SG> sorted the intermediate results and ranked them by the `rank` colum, I SG> could have just as easily said SG> SELECT * SG> from rankme SG> where country='UA' SG> ORDER BY rank SG> limit 10; -- Pavel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
