Aggregate: Max

Aggregate: Max

This aggregate function returns the largest of the numbers in the reference column (the column to which max is applied) as all duplicate rows are collapsed into unique single rows. Excluding the column to which the max is being applied, all rows are examined column-by-column in order to find identical rows.

The counterpart to this function is min.


The small dataset below will be used to show how max operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.

Column AColumn BColumn C

Applying the max function to Column C reduces the row count from four to two. The value in the column Max of Column C shows the maximum of of the Column C values in the duplicate rows that were collapsed during the operation.

In the dataset that is returned by the max function (shown below) number 6 in the first row resulted from the set of numbers {4, 5, 6}. Each of these numbers exists in this set because each was a member of an identical row when Column A and Column B were examined. (Column C was excluded from this examination because it is the reference column.) In this set of three numbers, 6 is the largest—therefore it became the value shown in the reference column.

The number 7 results from a set with a single number {7} because there were no duplicate rows that could contribute other numbers. Since 7 is both the minimum and maximum of the single-row set, the function returns a 7 for that row.

    0 Kudos
    0 Replies