MAX [Analytic]
Returns the maximum value of an expression within a window. The return value has the same type as the expression data type.
The analytic functions MIN()
and MAX()
can operate with Boolean values. The MAX()
function acts upon a Boolean Data Type or a value that can be implicitly converted to a Boolean value. If at least one input value is true, MAX()
returns t
(true). Otherwise, it returns f
(false). In the same scenario, the MIN()
function returns t
(true) if all input values are true. Otherwise, it returns f
.
Behavior Type
Syntax
MAX ( expression ) OVER ( [ window-partition-clause ] [ window-order-clause ] [ window-frame-clause ] )
Parameters
expression | Any expression for which the maximum value is calculated, typically a column reference. |
OVER()
|
Examples
The following query computes the deviation between the employees' annual salary and the maximum annual salary in Massachusetts:
=> SELECT employee_state, annual_salary, MAX(annual_salary) OVER(PARTITION BY employee_state ORDER BY employee_key) max, annual_salary- MAX(annual_salary) OVER(PARTITION BY employee_state ORDER BY employee_key) diff FROM employee_dimension WHERE employee_state = 'MA'; employee_state | annual_salary | max | diff ----------------+---------------+--------+--------- MA | 1918 | 995533 | -993615 MA | 2058 | 995533 | -993475 MA | 2586 | 995533 | -992947 MA | 2500 | 995533 | -993033 MA | 1318 | 995533 | -994215 MA | 2072 | 995533 | -993461 MA | 2656 | 995533 | -992877 MA | 2148 | 995533 | -993385 MA | 2366 | 995533 | -993167 MA | 2664 | 995533 | -992869 (10 rows)
The following example shows you the difference between the MIN
and MAX
analytic functions when you use them with a Boolean value. The sample creates a table with two columns, adds two rows of data, and shows sample output for MIN
and MAX
.
CREATE TABLE min_max_functions (emp VARCHAR, torf BOOL); INSERT INTO min_max_functions VALUES ('emp1', 1); INSERT INTO min_max_functions VALUES ('emp1', 0); SELECT DISTINCT emp, min(torf) OVER (PARTITION BY emp) AS worksasbooleanand, Max(torf) OVER (PARTITION BY emp) AS worksasbooleanor FROM min_max_functions; emp | worksasbooleanand | worksasbooleanor ------+-------------------+------------------ emp1 | f | t (1 row)