Hi! I have a table:
CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL auto_increment, `cucc` varchar(255) character set utf8 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test` (`id`, `cucc`) VALUES (1, 'egyszer'), (2, 'ketszer'), (3, 'ketszer'), (4, 'haromszor'), (5, 'haromszor'), (6, 'haromszor'), (7, 'negyszer'), (8, 'negyszer'), (9, 'negyszer'), (10, 'negyszer'); select * from test; +----+-----------+ | id | cucc | +----+-----------+ | 1 | egyszer | | 2 | ketszer | | 3 | ketszer | | 4 | haromszor | | 5 | haromszor | | 6 | haromszor | | 7 | negyszer | | 8 | negyszer | | 9 | negyszer | | 10 | negyszer | +----+-----------+ 10 rows in set (0.00 sec) Under 5.0.x version this query works good, the cnt column is right: set @row=0;select @row:=@row+1 as cnt,cucc,count(id)hany from test group by cucc order by hany desc; +------+-----------+------+ | cnt | cucc | hany | +------+-----------+------+ | 1 | negyszer | 4 | | 2 | haromszor | 3 | | 3 | ketszer | 2 | | 4 | egyszer | 1 | +------+-----------+------+ 4 rows in set (0.00 sec) Under 5.1.x or 5.5.x the cnt column is bad: set @row=0;select @row:=@row+1 as cnt,cucc,count(id)hany from test group by cucc order by hany desc; +------+-----------+------+ | cnt | cucc | hany | +------+-----------+------+ | 7 | negyszer | 4 | | 4 | haromszor | 3 | | 2 | ketszer | 2 | | 1 | egyszer | 1 | +------+-----------+------+ 4 rows in set (0.00 sec) Documentation ( http://dev.mysql.com/doc/refman/5.5/en/user-variables.html ) says this, so not a bug: "As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server." Is there any other solution to emulate row counter, which works with the above query under 5.1 and 5.5 mysql version? Thank you! Lay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
