OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
Re: Maintaining sort order with 'GROUP BY' and 'HAVING'

From: Nishikant Kapoor (nkapoorwebrachna.com)
Date: Thu Dec 04 2008 - 14:37:21 CST

Its been a while since I posted this. Would like to check again with the
folks on this list if they know why the results of query (b) are all
blank on MySQL 5.0.22. It works fine with 5.0.51a. If it is a bug in
5.0.22, can someone suggest a workaround for this (other than upgrading
MySQL).

Thanks,
Nishi

CREATE TABLE t ( id TINYINT, title VARCHAR(10) );
INSERT INTO t (id, title) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4,
'ddd'), (5, 'eee');

(a) DO sn := 0; SELECT sn:=sn+1 sn, id, title FROM t GROUP BY id
ORDER BY id desc;
| sn | id | title |
+------+------+-------+
| 1 | 5 | eee |
| 2 | 4 | ddd |
| 3 | 3 | ccc |
| 4 | 2 | bbb |
| 5 | 1 | aaa |

(b) [MySQL 5.0.51a]: DO sn := 0; SELECT sn:=sn+1 sn, id, title FROM t
HAVING sn=2 ORDER BY id desc;
+------+------+-------+
| sn | id | title |
+------+------+-------+
| 3 | 4 | ddd |
+------+------+-------+
1 row in set (0.00 sec)

(b) [MySQL 5.0.22]:
mysql> DO sn := 0; SELECT sn:=sn+1 sn, id, title FROM t HAVING sn=2
ORDER BY id desc;
Query OK, 0 rows affected (0.00 sec)

Empty set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql