List:General Discussion« Previous MessageNext Message »
From:Pavel Gulchouck Date:March 13 2010 7:43am
Subject:Re: Variable for row number?
View as plain text  
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
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