List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:March 14 2010 10:25pm
Subject:Re: Variable for row number?
View as plain text  
I know it's bad form to reply to yourself but I spotted something I 
could clarify

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

You do not need to sort by n24 in this last query. In fact, since we 
sorted the intermediate results and ranked them by the `rank` colum, I 
could have just as easily said

SELECT *
from rankme
where country='UA'
ORDER BY rank
limit 10;

Yours,
-- 
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
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