Which is the least expensive aggregate function in the absence of ANY()

Question

I usally use MAX() or MIN() if a DBMS hasn't an ANY() aggregate function.

Is there something less expensive in mySQL and MS-SQL?

Answer 1

As of MySQL 5.7 (released October 2015), there is actually a function for this! any_value(col) explicitly meets this need - see the documentation for details.

However, it's important to note that it still appears to not guarantee a short-circuit and may still perform a full scan, so the goal of higher efficiency may not be met. If MySQL is intelligent about it, there is some chance, though testing would be wise. There are two circumstances in which it may be helpful anyway:

• If the value to be aggregated is particularly large and unindexed, such that any comparisons would be expensive.
• If the intention in the code would be made less clear by leveraging max or another placeholder aggregate. For example, a max may imply there was some reason for its existence rather than getting "anything" from the grouping, but any_value would explicitly define the intention without additional commenting.

Toy sample:

select
col, 
any_value(a.val) as any_val
from (
    select 'blue' as col, 3 as val
    union all
    select 'blue' as col, 2 as val
    union all
    select 'green' as col, 1 as val
) as a
group by col

Answer 2

MIN and MAX are equally (in)expensive.

Answer 3

MySQL does not need an ANY() aggregate.

if I do a

SELECT field1, field2, SUM(field3) FROM table1 
GROUP BY field2

Microsofts T-SQL will complain but

MySQL will just silently execute

SELECT whatever(field1), field2, SUM(.... 

Which of course is way faster than SELECT max(field1), field2, SUM(.... GROUP BY field2

MySQL supports ANY, but SELECT ANY(field1) ... doesn't work because ANY is an operator similar to IN and ALL.
see: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

I love MySQL