From: Carsten Pedersen
Date: March 12 2010 10:37pm
Subject: Re: Variable for row number?
List-Archive: http://lists.mysql.com/mysql/220945
Message-Id: <4B9AC21C.80709@bitbybit.dk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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