From: Carsten Pedersen (carstenbitbybit.dk)
Date: Fri Mar 12 2010 - 16:37:16 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]