MIN [Analytic]

Returns the minimum 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 minimum value is calculated, typically a column reference.
OVER()

See Analytic Functions.

Examples

The following example shows how you can query to determine the deviation between the employees' annual salary and the minimum annual salary in Massachusetts:

=> SELECT employee_state, annual_salary,
      MIN(annual_salary) 
      OVER(PARTITION BY employee_state ORDER BY employee_key) min,
        annual_salary- MIN(annual_salary) 
      OVER(PARTITION BY employee_state ORDER BY employee_key) diff 
   FROM employee_dimension 
   WHERE employee_state = 'MA';
 employee_state | annual_salary | min  | diff
----------------+---------------+------+------
 MA             |          1918 | 1204 |  714
 MA             |          2058 | 1204 |  854
 MA             |          2586 | 1204 | 1382
 MA             |          2500 | 1204 | 1296
 MA             |          1318 | 1204 |  114
 MA             |          2072 | 1204 |  868
 MA             |          2656 | 1204 | 1452
 MA             |          2148 | 1204 |  944
 MA             |          2366 | 1204 | 1162
 MA             |          2664 | 1204 | 1460
(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)

 

See Also