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