I'm using MySQL 5.1.30 (WinXP) and user defined variables don't seem to be
working properly if the Select statement is sorted.
Here is a simple example:
CREATE TABLE `tmp` (
`Purch_Date` date DEFAULT NULL,
`Product` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Here are the values that I entered in this order:
Purch_Date Product
2007-01-01 A
2007-01-04 B
2008-05-04 B
2008-12-04 B
2001-01-04 D
2001-01-04 C
Now here is the Select statement that is failing:
set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date
OUTPUT:
@num:=...@num+1 Purch_Date
5 2001-01-04
1 2007-01-01
2 2007-01-04
3 2008-05-04
4 2008-12-04
The earliest date "2001-01-04" should have 1 and not 5 as the first column
in the results.
Why isn't the column "@num:=...@num+1" sorted in ascending order since it was
grouped by purch_date? Even if I explicitly tell it to sort by purch_date:
set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date
order by purch_date;
I still get the same results. This has worked fine in MySQL 5.0.1 but is
giving me these strange results in MySQL 5.1.30.
Am I doing something wrong here?
TIA
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
