Vertica Analytics Platform Version 9.2.x Documentation

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

Immutable

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()

See Analytic Functions.

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)