Pavel Gulchouck wrote:
On Fri, Mar 12, 2010 at 11:37:16PM +0100, Carsten Pedersen writes:
CP> Pavel Gulchouck skrev:
Is there any way to get sequence row number in request?
I need row number calculated before "having" but after "group by"
and "order", so "select @row := @row+1" unsuitable in my case
(it executed before grouping).
CP> something along the lines of this:
[...]
CP> mysql> select @a:=0; select @a:=...@a+1, c1, s from (select c1, sum(c2) as
s from t group by c1) _d;
CP> +-------+
CP> | @a:=0 |
CP> +-------+
CP> | 0 |
CP> +-------+
CP> 1 row in set (0.00 sec)
CP> +----------+------+------+
CP> | @a:=...@a+1 | c1 | s |
CP> +----------+------+------+
CP> | 1 | 27 | 5 |
CP> | 2 | 35 | 7 |
CP> +----------+------+------+
CP> 2 rows in set (0.00 sec)
Thanks!
Unfortunately in my tests "@a:=...@a+1" executed after "having", I do not
understand this phenomenon.
Without "having":
mysql> set @a:=0; select @a:=...@a+1, asn, country, n24 from (select asn,
country, avg(n24) as n24 from asrank join asname using (asn) group by asn) as s
order by n24 desc limit 10;
Query OK, 0 rows affected (0.00 sec)
+----------+------+---------+--------------+
| @a:=...@a+1 | asn | country | n24 |
+----------+------+---------+--------------+
| 1 | 1239 | US | 3717878.8333 |
| 2 | 3356 | US | 3711349.5000 |
| 3 | 3549 | US | 1980916.5000 |
| 4 | 7018 | US | 1906332.3333 |
| 5 | 701 | US | 1709513.7500 |
| 6 | 1299 | EU | 1420692.0833 |
| 7 | 3561 | US | 1336558.1667 |
| 8 | 2914 | US | 1180490.9167 |
| 9 | 3257 | DE | 1161872.0833 |
| 10 | 6453 | CA | 1072615.5000 |
+----------+------+---------+--------------+
10 rows in set (4.32 sec)
Add "having" condition:
mysql> set @a:=0; select @a:=...@a+1, asn, country, n24 from (select asn,
country, avg(n24) as n24 from asrank join asname using (asn) group by asn) as s
having country='UA' order by n24 desc limit 10;
Query OK, 0 rows affected (0.00 sec)
+----------+-------+---------+-----------+
| @a:=...@a+1 | asn | country | n24 |
+----------+-------+---------+-----------+
| 1 | 13249 | UA | 4683.6667 |
| 2 | 35320 | UA | 4658.2500 |
| 3 | 6849 | UA | 4191.1667 |
| 4 | 21219 | UA | 3802.0833 |
| 5 | 25229 | UA | 3006.3333 |
| 6 | 3255 | UA | 2351.9167 |
| 7 | 12883 | UA | 1751.0000 |
| 8 | 29632 | UA | 1623.1667 |
| 9 | 21011 | UA | 1318.6667 |
| 10 | 12383 | UA | 1119.5000 |
+----------+-------+---------+-----------+
10 rows in set (4.99 sec)
Number are sequenced again, in result table, but I need numbers in
total rating, for all countries.
Solved by also one select. It works but I'm afraid it's not effective:
mysql> set @a:=0; select rank, asn, country, n24 from (select @a:=...@a+1 as
rank, asn, country, n24 from (select asn, country, avg(n24) as n24 from asrank
join asname using (asn) group by asn) as s order by n24 desc) as s1 where
country='UA' limit 10;
Query OK, 0 rows affected (0.00 sec)
+------+-------+---------+-----------+
| rank | asn | country | n24 |
+------+-------+---------+-----------+
| 424 | 13249 | UA | 4683.6667 |
| 426 | 35320 | UA | 4658.2500 |
| 475 | 6849 | UA | 4191.1667 |
| 511 | 21219 | UA | 3802.0833 |
| 614 | 25229 | UA | 3006.3333 |
| 708 | 3255 | UA | 2351.9167 |
| 843 | 12883 | UA | 1751.0000 |
| 882 | 29632 | UA | 1623.1667 |
| 1000 | 21011 | UA | 1318.6667 |
| 1100 | 12383 | UA | 1119.5000 |
+------+-------+---------+-----------+
10 rows in set (4.27 sec)
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.
--
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
