How to use a @variable with min/max functions?

Question

Edit: narrowed the problem down. here's a simpler example:

mysql> select * from table_a;
+-------+
| col_a |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
+-------+
4 rows in set (0.00 sec)

mysql> select @a:=max(col_a),@a from table_a;
+----------------+------+
| @a:=max(col_a) | @a   |
+----------------+------+
|              4 | NULL |
+----------------+------+
1 row in set (0.00 sec)

Why is @a NULL, and how can I get it to not be?

Answer 1

MySql manual is quite clear about this, quote:

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. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

Answer 2

would not every column return by aggregate function can be used for most of the cases?

in another words, is hard to guess what you are truly looking for
(dun mind to update your question again?)

the normal usage

select max(col_a) as anything
from table_a
order by anything;

i dun see any harm for this either

select max(col_a) as anything, max(col_a) as anything2, 
from table_a
order by anything, anything2;

if you are trying to use the aggregate function for LIMIT,
this is not doable

user defined variables from select is not reliable (I used to be a fan)

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

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. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.