>>>Right... what we need is an aggregate function any(), which would mean "the first value you get, I don't care in which order". That one would be fast, and simpler than min() or max().
>>
>>Sorry Dragan,
>>
>>but I cannot see the usability of this random aggregate function.
>
>What's the usability of calculating the max() of a character field in a lookup table, which is the same for all records in a group? This would do the same, just much faster.
I have now seen your thoughts.
If you known that on the group the expression is constant, then
min=max=any and these are equals:
SELECT item,MIN(exp) FROM test GROUP BY item
SELECT item,MAX(exp) FROM test GROUP BY item
SELECT item,exp FROM test GROUP BY item,exp
SELECT item,exp FROM test GROUP BY exp,item
set engine 70
SELECT item,exp FROM test GROUP BY item
This is a very special case,
and a lot of times it conceals a bad planning of the database;
then it is difficult to justify the implementation of ANY()
Besides, the most powerful databaseses (MSSQL,..) succeed in optimizing
the standard description (with the subquery)
and they implement of fact a function FIRST().