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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
