List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:March 12 2010 10:37pm
Subject:Re: Variable for row number?
View as plain text  
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
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