​
Overview

The MIN() window function is used to compute the minimum value of an expression across a set of rows defined by a window specification.

​
Syntax

The syntax for this function is as follows:

MIN ([ALL] expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    RANGE BETWEEN start_value AND end_value
)

​
Parameters

  • ALL: retains all duplicate values from the expression

​
Example

For the needs of this section we will use a simplified version of the film table from the Pagila database, containing only the title, length and rating columns. The complete schema for the film table can be found on the Pagila database website.

DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  rating text,
  length int
);
INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 'PG-13'),
  ('CHRISTMAS MOONSHINE', 150, 'NC-17'),
  ('DANGEROUS UPTOWN', 121, 'PG'),
  ('KILL BROTHERHOOD', 54, 'G'),
  ('HALLOWEEN NUTS', 47, 'PG-13'),
  ('HOURS RAGE', 122, 'NC-17'),
  ('PIANIST OUTFIELD', 136, 'NC-17'),
  ('PICKUP DRIVING', 77, 'G'),
  ('INDEPENDENCE HOTEL', 157, 'NC-17'),
  ('PRIVATE DROP', 106, 'PG'),
  ('SAINTS BRIDE', 125, 'G'),
  ('FOREVER CANDIDATE', 131, 'NC-17'),
  ('MILLION ACE', 142, 'PG-13'),
  ('SLEEPY JAPANESE', 137, 'PG'),
  ('WRATH MILE', 176, 'NC-17'),
  ('YOUTH KICK', 179, 'NC-17'),
  ('CLOCKWORK PARADISE', 143, 'PG-13');

The query below uses the MIN() to find the minimum length of films for each rating category and also calculates a running minimum length of films ordered by their length.

SELECT
   title,
   length,
   rating,
   MIN(length) OVER ( PARTITION BY rating ) AS MinLengthByRating,
   MIN(length) OVER ( 
ORDER BY
   length ROWS BETWEEN unbounded preceding AND CURRENT ROW ) AS RunningMinLength 
FROM film 
ORDER BY length;

By running the above code, we will get the following output:

        title        | length | rating | minlengthbyrating | runningminlength 
---------------------+--------+--------+-------------------+------------------
 HALLOWEEN NUTS      |     47 | PG-13  |                47 |               47
 KILL BROTHERHOOD    |     54 | G      |                54 |               47
 PICKUP DRIVING      |     77 | G      |                54 |               47
 ATTRACTION NEWTON   |     83 | PG-13  |                47 |               47
 PRIVATE DROP        |    106 | PG     |               106 |               47
 DANGEROUS UPTOWN    |    121 | PG     |               106 |               47
 HOURS RAGE          |    122 | NC-17  |               122 |               47
 SAINTS BRIDE        |    125 | G      |                54 |               47
 FOREVER CANDIDATE   |    131 | NC-17  |               122 |               47
 PIANIST OUTFIELD    |    136 | NC-17  |               122 |               47
 SLEEPY JAPANESE     |    137 | PG     |               106 |               47
 MILLION ACE         |    142 | PG-13  |                47 |               47
 CLOCKWORK PARADISE  |    143 | PG-13  |                47 |               47
 CHRISTMAS MOONSHINE |    150 | NC-17  |               122 |               47
 INDEPENDENCE HOTEL  |    157 | NC-17  |               122 |               47
 WRATH MILE          |    176 | NC-17  |               122 |               47
 YOUTH KICK          |    179 | NC-17  |               122 |               47
(17 rows)
MAXNTH_VALUE