List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:March 14 2010 8:21pm
Subject:Re: Variable for row number?
View as plain text  
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
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