Hi!
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).
--
Pavel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Pavel Gulchouck skrev:
> Hi!
>
> 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).
something along the lines of this:
mysql> select * from t;
+------+------+
| c1 | c2 |
+------+------+
| 27 | 2 |
| 27 | 3 |
| 35 | 3 |
| 35 | 4 |
+------+------+
4 rows in set (0.00 sec)
mysql> select c1, sum(c2) as s from t group by c1;
+------+------+
| c1 | s |
+------+------+
| 27 | 5 |
| 35 | 7 |
+------+------+
2 rows in set (0.00 sec)
mysql> select @a:=0; select @a:=@a+1, c1, s from (select c1, sum(c2) as
s from t group by c1) _d;
+-------+
| @a:=0 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
+----------+------+------+
| @a:=@a+1 | c1 | s |
+----------+------+------+
| 1 | 27 | 5 |
| 2 | 35 | 7 |
+----------+------+------+
2 rows in set (0.00 sec)
Best,
/ Carsten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
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=gcdmg-mysql-2@m.gmane.org
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=gcdmg-mysql-2@m.gmane.org
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=gcdmg-mysql-2@m.gmane.org
On Sun, Mar 14, 2010 at 06:25:31PM -0400, Shawn Green writes:
SG> I know it's bad form to reply to yourself but I spotted something I
SG> could clarify
Thank you very much, it works good enough in this case!
SG> 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.
SG> You do not need to sort by n24 in this last query. In fact, since we
SG> sorted the intermediate results and ranked them by the `rank` colum, I
SG> could have just as easily said
SG> SELECT *
SG> from rankme
SG> where country='UA'
SG> ORDER BY rank
SG> limit 10;
--
Pavel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org