{"id":960,"date":"2024-12-15T10:38:04","date_gmt":"2024-12-15T03:38:04","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=960"},"modified":"2025-01-14T10:26:16","modified_gmt":"2025-01-14T03:26:16","slug":"postgresql-min","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-aggregate-functions\/postgresql-min\/","title":{"rendered":"PostgreSQL MIN Aggregate Function"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you’ll learn how to use the PostgreSQL MIN<\/code> aggregate function to find the smallest value in a set of values.<\/p>\n\n\n\n

Getting Started with the PostgreSQL MIN Aggregate Function #<\/a><\/h2>\n\n\n\n

In PostgreSQL, the MIN<\/code> aggregate function<\/a> accepts a set of values and returns the smallest one. <\/p>\n\n\n\n

The MIN<\/code> function is flexible and can apply to numbers, dates, and strings, allowing you to work with various data types.<\/p>\n\n\n\n

Basic Syntax #<\/a><\/h3>\n\n\n\n

The following shows how to use the MIN<\/code> aggregate function to find the smallest value in a column of a table:<\/p>\n\n\n

SELECT<\/span>\n  MIN(column<\/span>)\nFROM<\/span>\n  table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using MIN with GROUP BY clause #<\/a><\/h3>\n\n\n\n
To find the smallest values of each group, you can use the MIN<\/code> function with the GROUP BY<\/a><\/code> clause:<\/p>\n\n\n
SELECT<\/span>\n  column1,\n  MIN(column2)\nFROM<\/span>\n  table_name<\/span>\nGROUP<\/span> BY<\/span>\n  column1;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
    \n
  1. The GROUP BY<\/code> clause categorizes the values into groups by the values in column1<\/code>.<\/li>\n\n\n\n
  2. The MIN<\/code> function returns the smallest value in the column2<\/code> for each group.<\/li>\n<\/ol>\n\n\n\n
    This query comes in handy when you want to get insights from your data.<\/p>\n\n\n\n
    Using Expressions with MIN function #<\/a><\/h3>\n\n\n\n
    Besides a table column, you can use an expression in the MIN<\/code> function:<\/p>\n\n\n
    SELECT<\/span>\n  MIN(expression)\nFROM<\/span>\n  table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this syntax, the expression may involve table columns with operators.<\/p>\n\n\n\n
    PostgreSQL MIN Aggregate Function Examples #<\/a><\/h2>\n\n\n\n
    Let’s explore some examples of using the MIN<\/code> aggregate function.<\/p>\n\n\n\n
    Finding the Lowest Product Price #<\/a><\/h3>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    The following example uses the MIN<\/code> function to find the lowest price of all products<\/p>\n\n\n
    SELECT<\/span>\n  MIN(price)\nFROM<\/span>\n  products;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/strong><\/p>\n\n\n
      min\n--------<\/span>\n 199.99<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example, the MIN<\/code> function examines all values in the price<\/code> column of the products<\/code> table and returns the smallest one.<\/p>\n\n\n\n
    Finding the Product with the Lowest Price #<\/a><\/h3>\n\n\n\n
    To find the product with the lowest price, you can use a subquery<\/a>:<\/p>\n\n\n
    SELECT<\/span>\n  product_name,\n  price\nFROM<\/span>\n  products\nWHERE<\/span>\n  price = (\n    SELECT<\/span>\n      MIN(price)\n    FROM<\/span>\n      products\n  );<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/strong><\/p>\n\n\n
           product_name        | price\n---------------------------+--------<\/span>\n Samsung Galaxy Buds Pro 2<\/span> | 199.99<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example:<\/p>\n\n\n\n
      \n
    1. The subquery returns the lowest price.<\/li>\n\n\n\n
    2. The outer query finds the product whose price matches the lowest price.<\/li>\n<\/ol>\n\n\n\n
      The query will return more than one product if these products have the same lowest price.<\/p>\n\n\n\n
      Finding Products with the Lowest Prices in Each Category #<\/a><\/h3>\n\n\n\n
      The following example uses the MIN<\/code> aggregate function to find the product with the lowest price in each category:<\/p>\n\n\n
      SELECT<\/span>\n  category_id,\n  MIN(price) AS<\/span> min_price\nFROM<\/span>\n  products\nGROUP<\/span> BY<\/span>\n  category_id\nORDER<\/span> BY<\/span>\n  min_price;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/strong><\/p>\n\n\n
       category_id | min_price\n-------------+-----------<\/span>\n           5<\/span> |    199.99<\/span>\n           6<\/span> |    349.99<\/span>\n           9<\/span> |    399.99<\/span>\n           4<\/span> |    699.99<\/span>\n           3<\/span> |    799.99<\/span>\n          12<\/span> |    999.99<\/span>\n          11<\/span> |   1299.99<\/span>\n           8<\/span> |   1999.99<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
      In this example:<\/p>\n\n\n\n
      Try it<\/a><\/p>\n\n\n\n
    Try it<\/a><\/p>\n\n\n\n
    Try it<\/a><\/p>\n\n\n\n
It’s important to note that the MIN<\/code> function ignores NULL<\/a><\/code> when determining the minimum value.<\/p>\n\n\n\n