Re: Maintaining sort order with 'GROUP BY' and 'HAVING'
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:[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) [MySQL 5.0.51a]: DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maintaining sort order with 'GROUP BY' and 'HAVING'
Hi Nishi, I think what's more relevant is why did you downgrade to 5.0.22? The sorting issue you're experiencing was fixed for the 5.0.67 community release. Many bugs will have been fixed between 5.0.22 and 5.0.51a; downgrading is not a solution. Upgrade. If this is a new system you could even consider going to 5.1.30. Andy Nishikant Kapoor wrote: 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:[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) [MySQL 5.0.51a]: DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maintaining sort order with 'GROUP BY' and 'HAVING'
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]
Re: Maintaining sort order with 'GROUP BY' and 'HAVING'
Hi Nishi, There was a bug in that version that affected the sort order when combined with a group by statement (http://bugs.mysql.com/bug.php?id=32202.) There are a couple of workarounds but they are bad (e.g. removing primary key!) I would strongly suggest you upgrade to 5.0.67 or possibly even the newly-released 5.1.30, depending on how many applications you have that are dependent on that box. Regards, Andy 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]
Re: Maintaining sort order with 'GROUP BY' and 'HAVING'
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]
Re: Maintaining sort order with 'GROUP BY' and 'HAVING'
Thanks for the response, Andy. I am now using the SELECT subquery to fix that problem. However, when I run the following query (b) on 5.0.22, I don't get any results - with and without the fix. 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) [MySQL 5.0.51a]: DO @sn := 0; SELECT @sn:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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) Can someone please help me understand it? Thanks, Nishi Andy Shellam wrote: Hi Nishi, There was a bug in that version that affected the sort order when combined with a group by statement (http://bugs.mysql.com/bug.php?id=32202.) There are a couple of workarounds but they are bad (e.g. removing primary key!) I would strongly suggest you upgrade to 5.0.67 or possibly even the newly-released 5.1.30, depending on how many applications you have that are dependent on that box. Regards, Andy 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]
