Geek Answers Handbook

What is MAX (DISTINCT x) in SQL?

I just stumbled upon the jOOQ maxDistinct SQL aggregation function .

How is MAX(DISTINCT x) different from MAX(x) ?

+6
source share
4 answers

maxDistinct and minDistinct were defined in order to maintain consistency with other aggregate functions, where the actual difference is different (for example, countDistinct , sumDistinct ).

Since the maximum (or minimum) calculated between the individual values ​​of the data set is mathematically equivalent to the simple maximum (or minimum) of the same set, these functions are essentially redundant.

+4
source

In short, there will be no difference. In the case of MySQL, he even stated on the manual page :

Returns the maximum value of expr. MAX () may take a string argument; in such cases, it returns the maximum string value. See Section 8.5.3, “How MySQL Uses Indexes”. The DISTINCT keyword can be used to search for a maximum of individual expr values, however this results in the same result as the DISTINCT exception.

The reason this is possible is to maintain compatibility with other platforms. There will be no difference inside - MySQL will simply omit the influence of DISTINCT . He will not try to do something with a set of strings (i.e. create an excellent set first). For indexed columns, this will be Select tables optimized away (thus, reading a single value from the index, not the table), for non-indexed columns, a full scan.

+1
source

If I am not mistaken, there is no difference

For columns

 ID 1 2 2 3 3 4 5 5

OUTPUT for both requests equally 5

 MAX(DISTINCT x) // ID = 1,2,2,3,3,4,5,5 // DISTINCT = 1,2,3,4,5 // MAX = 5 // 1 row

and for

 MAX(x) // ID = 1,2,2,3,3,4,5,5 // MAX = 5 // 1 row
0
source

Theoretically, DISTINCT x ensures that each element is different from a specific set. The max statement selects the maximum value from the set. In plain SQL there should be no difference between the two.

0
source

More articles:


All Articles