Product Documentation

Appearance

MIN (window function) ​

Compute the minimum value for an expression based on a window definition and an optional frame clause.

MIN ([ expression ]) OVER { window_name | ( [ window_definition ] ) }

For the full window_definition syntax, see Syntax for Window Functions.

Note: The input expression for a MIN function cannot be a CHAR, VARCHAR, or BOOLEAN data type.

For example, return the minimum capacity from the team table and partition the results by the city column:

premdb=# select city, capacity, 
min(capacity) over(partition by city order by capacity) 
from team 
where city in('London','Birmingham','Liverpool') 
order by city;
   city    | capacity |  min  
------------+----------+-------
 Birmingham |    27000 | 27000
 Birmingham |    30016 | 27000
 Birmingham |    42785 | 27000
 Liverpool  |    40221 | 40221
 Liverpool  |    44742 | 40221
 London     |    18439 | 18439
 London     |    25700 | 18439
 London     |    26255 | 18439
 London     |    26255 | 18439
 London     |    27111 | 18439
 London     |    35016 | 18439
 London     |    36284 | 18439
 London     |    41663 | 18439
 London     |    60260 | 18439
(14 rows)

Add an explicit window frame to this example:

premdb=# select city, capacity, min(capacity) 
over(partition by city order by capacity rows between 2 preceding and 2 following) 
from team where city in('London','Birmingham','Liverpool') order by city;
   city    | capacity |  min  
------------+----------+-------
 Birmingham |    27000 | 27000
 Birmingham |    30016 | 27000
 Birmingham |    42785 | 27000
 Liverpool  |    40221 | 40221
 Liverpool  |    44742 | 40221
 London     |    18439 | 18439
 London     |    25700 | 18439
 London     |    26255 | 18439
 London     |    26255 | 25700
 London     |    27111 | 26255
 London     |    35016 | 26255
 London     |    36284 | 27111
 London     |    41663 | 35016
 London     |    60260 | 36284
(14 rows)

Parent topic:Window Functions