Looking further for more info on this issue reveals that there is a bug
in MySQL 5.0.51:
http://bugs.mysql.com/bug.php?id=32202
which got fixed in 5.0.52.
Thanks,
Nishi
Nishikant Kapoor wrote:
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:[EMAIL PROTECTED] 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) DO @sn := 0;
SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t GROUP BY id HAVING sn=2 ORDER
BY id desc;
| 2 | 2 | bbb |
(c) DO @sn := 0;
SELECT @sn:[EMAIL PROTECTED] sn, id, title FROM t HAVING sn=2 ORDER BY id desc;
| 3 | 4 | ddd |
I do not understand why (b) is not able to retain the same order of sn
as in (a). I am assuming it is the 'HAVING sn=2' that is forcing (b)
to reset the sort order. However, (c) is able to maintain the same sn
order as in (a) even with 'HAVING sn=2' clause in it, although it is
not using the 'GROUP BY id' anymore.
Is there a way I can retain the sn sort order in (b) same as in (a)?
| version() |
+-----------+
| 5.0.51a |
Thanks,
Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
