Max(distinct...) in MySQL?

Question

According to the documentation, in MySQL the Max() and Min() aggregate functions accept a DISTINCT keyword:

The DISTINCT keyword can be used to find the maximum of the distinct values of expr, however, this produces the same result as omitting DISTINCT.

Maybe I'm missing something, but how could this ever be useful?

UPDATE:

MySQL does not, contrary to several answers, support distinct for every aggregate function.

Distinct is supported for avg(), count(), min(), max(), group_concat(), and sum(). It is not supported for std(), variance(), several other statistical functions, or for the bitwise aggregate functions.

Answer 1

They take DISTINCT as all aggregates do, however, the fact that there might be more than one of the values is irrelevent when you're after the maximum value therefore it would always be the same.

Count is the key example of where distinct will produce different results.

Answer 2

The maximum value of a distinct set would be exactly the same as that of the same set with duplicates, by definition.

But to answer your question, it might be useful in combination with other aggregate functions in the select clause operating on the same distinct values.

Answer 3

I think it is just generally allowed / supported to put DISTINCT into aggregate functions, even if it doesn't make a difference in the result.

Answer 4

I'm scratching my head at it also; the only thing I can come up with is that select max(foo) might do a table scan to find the maximum value but select max(distinct foo) might go to an index first. Maybe it's faster for certain table sizes?

Answer 5

For what it's worth, the ANSI SQL standard specifies that all aggregate functions accept an optional DISTINCT keyword. In the case of MIN() and MAX() this seems meaningless, since the result is the same either way.

But for other aggregate functions, such as COUNT(), SUM(), and AVG(), the DISTINCT option may affect the result.

I would assume that standard SQL calls for every aggregate function to support DISTINCT to simplify the syntax of the language. It does no harm to support DISTINCT where it has no effect, and any implementation is free to treat it as a no-op internally.

Answer 6

As far as I know, the SQL92 standard has Five aggregate functions defined. Those five are avg, count, max, min, and sum. Coincidentally*, those all accept the DISTINCT keyword.

*and by that I mean "not coincidentally"