List:General Discussion« Previous MessageNext Message »
From:Pavel Gulchouck Date:March 15 2010 5:03pm
Subject:Re: Variable for row number?
View as plain text  
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
Thread
Variable for row number?Pavel Gulchouck12 Mar
  • Re: Variable for row number?Carsten Pedersen12 Mar
    • Re: Variable for row number?Pavel Gulchouck13 Mar
      • Re: Variable for row number?Shawn Green14 Mar
        • Re: Variable for row number?Shawn Green14 Mar
          • Re: Variable for row number?Pavel Gulchouck15 Mar