mos wrote:
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
I suspect that what's happening is MySQL isn't selecting the rows in
order but displaying them ordered by purch_date. That is, the other
column value is being assigned to its row *before* the ordering occurs.
That's just a guess though, really. I might be way off here.
You could probably test this by selecting and ordering purch_date in a
sub-query and assigning your other column to the results of that.
(I think)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
