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
