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