MIN Function

Calculates the smallest value of the values that are not NULL in expr. If all values in expr are NULL, NULL is returned.

Syntax

Aggregate Function

MIN([DISTINCT] expr)

Window Function

MIN(expr) OVER ([PARTITION BY part]
                 [ORDER BY ord [ASC|DESC] [window_frame]]
                )

Arguments

expr: A (qualified) column name.

---

part (optional): Expression to partition by.

---

ord (optional): Expression to sort within each partition.

---

window_frame (cumulative or sliding): Defines the subset of rows within the window for which the aggregated values should be returned.

Usage Notes

As a window function (with an OVER clause):

• A window frame (cumulative or sliding) must be defined. The default value for the window frame, if not specified, is a cumulative window frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• The use of the DISTINCT keyword when this function is used as a window function is ignored.

Examples

Example 1 | Aggregate Function

SELECT  MIN(col1) AS result1,
        MIN(DISTINCT col1) AS result2,
        MIN(col2) AS result3,
        MIN(DISTINCT col2) AS result4,
        MIN(ord1) AS result5, 
        MIN(grp1) AS result6
FROM    (SELECT 'a' AS ord1, 'g1' AS grp1, '10' AS col1, 10 AS col2
         UNION ALL 
         SELECT 'b' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
         UNION ALL
         SELECT 'c' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
         UNION ALL 
         SELECT 'a' AS ord1, 'g2' AS grp1, '30' AS col1, NULL AS col2 
         UNION ALL 
         SELECT 'b' AS ord1, 'g2' AS grp1, '30' AS col1, NULL AS col2 
         UNION ALL 
         SELECT 'c' AS ord1, 'g2' AS grp1, '40' AS col1, 40 AS col2
         UNION ALL 
         SELECT 'a' AS ord1, 'g3' AS grp1, '50' AS col1, NULL AS col2
         UNION ALL 
         SELECT 'b' AS ord1, 'g3' AS grp1, '50' AS col1, NULL AS col2
         UNION ALL 
         SELECT 'c' AS ord1, 'g3' AS grp1, '60' AS col1, 60 AS col2
         UNION ALL
         SELECT 'd' AS ord1, 'g3' AS grp1, '50' AS col1, 50 AS col2);

+---------+---------+---------+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 | RESULT5 | RESULT6 |
+---------+---------+---------+---------+---------+---------+
| 10      | 10      | 10      | 10      | a       | g1      |
+---------+---------+---------+---------+---------+---------+

Example 2 | Window Function

SELECT col1, grp1, ord1,
       MIN(col1) OVER(PARTITION BY grp1) AS result1,
       MIN(col1) OVER(PARTITION BY grp1 ORDER BY ord1 ASC) AS result2,
       MIN(col1) OVER(PARTITION BY grp1 ORDER BY ord1 DESC) AS result3
FROM    (SELECT 'a' AS ord1, 'g1' AS grp1, '10' AS col1, 10 AS col2
         UNION ALL 
         SELECT 'b' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
         UNION ALL
         SELECT 'c' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
         UNION ALL 
         SELECT 'a' AS ord1, 'g2' AS grp1, '30' AS col1, NULL AS col2 
         UNION ALL 
         SELECT 'b' AS ord1, 'g2' AS grp1, '30' AS col1, NULL AS col2 
         UNION ALL 
         SELECT 'c' AS ord1, 'g2' AS grp1, '40' AS col1, 40 AS col2
         UNION ALL 
         SELECT 'a' AS ord1, 'g3' AS grp1, '50' AS col1, NULL AS col2
         UNION ALL 
         SELECT 'b' AS ord1, 'g3' AS grp1, '50' AS col1, NULL AS col2
         UNION ALL 
         SELECT 'c' AS ord1, 'g3' AS grp1, '60' AS col1, 60 AS col2
         UNION ALL
         SELECT 'd' AS ord1, 'g3' AS grp1, '50' AS col1, 50 AS col2);

+------+------+------+---------+---------+---------+
| COL1 | GRP1 | ORD1 | RESULT1 | RESULT2 | RESULT3 |
+------+------+------+---------+---------+---------+
| 10   | g1   | a    | 10      | 10      | 10      |
| 20   | g1   | b    | 10      | 10      | 20      |
| 20   | g1   | c    | 10      | 10      | 20      |
| 30   | g2   | a    | 30      | 30      | 30      |
| 30   | g2   | b    | 30      | 30      | 30      |
| 40   | g2   | c    | 30      | 30      | 40      |
| 50   | g3   | a    | 50      | 50      | 50      |
| 50   | g3   | b    | 50      | 50      | 50      |
| 60   | g3   | c    | 50      | 50      | 50      |
| 50   | g3   | d    | 50      | 50      | 50      |
+------+------+------+---------+---------+---------+

Example 3 | Window Function with WINDOWING

In this example, a sliding window with a width of 4 rows is created. For this window, the highest value is returned. A single partition is used, so there is no PARTITION BY clause in the OVER() clause.

SELECT col1, 
       grp1, 
       MIN(col1) OVER(ORDER BY col1, grp1 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS result1
FROM    (SELECT 'a' AS ord1, 'g1' AS grp1, '10' AS col1, 10 AS col2
         UNION ALL 
         SELECT 'b' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
         UNION ALL
         SELECT 'c' AS ord1, 'g1' AS grp1, '20' AS col1, 20 AS col2
         UNION ALL 
         SELECT 'a' AS ord1, 'g2' AS grp1, '30' AS col1, NULL AS col2 
         UNION ALL 
         SELECT 'b' AS ord1, 'g2' AS grp1, '30' AS col1, NULL AS col2 
         UNION ALL 
         SELECT 'c' AS ord1, 'g2' AS grp1, '40' AS col1, 40 AS col2
         UNION ALL 
         SELECT 'a' AS ord1, 'g3' AS grp1, '50' AS col1, NULL AS col2
         UNION ALL 
         SELECT 'b' AS ord1, 'g3' AS grp1, '50' AS col1, NULL AS col2
         UNION ALL 
         SELECT 'c' AS ord1, 'g3' AS grp1, '60' AS col1, 60 AS col2
         UNION ALL
         SELECT 'd' AS ord1, 'g3' AS grp1, '50' AS col1, 50 AS col2);

+------+------+---------+
| COL1 | GRP1 | RESULT1 |
+------+------+---------+
| 10   | g1   | 10      |
| 20   | g1   | 10      |
| 20   | g1   | 10      |
| 30   | g2   | 10      |
| 30   | g2   | 20      |
| 40   | g2   | 20      |
| 50   | g3   | 30      |
| 50   | g3   | 30      |
| 50   | g3   | 40      |
| 60   | g3   | 50      |
+------+------+---------+