{"id":968,"date":"2024-12-15T15:00:18","date_gmt":"2024-12-15T08:00:18","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=968"},"modified":"2025-01-05T19:40:03","modified_gmt":"2025-01-05T12:40:03","slug":"postgresql-max","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-aggregate-functions\/postgresql-max\/","title":{"rendered":"PostgreSQL MAX Aggregate Function"},"content":{"rendered":"\n

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

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

In PostgreSQL, the MAX<\/code> aggregate function accepts a set of values and returns the maximum value.<\/p>\n\n\n\n

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

SELECT<\/span>\n  MAX(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
Finding Maximum Values for Groups #<\/a><\/h3>\n\n\n\n
To find the maximum values for each group, you can use the MAX<\/code> function with the GROUP BY<\/a><\/code> clause:<\/p>\n\n\n
SELECT<\/span>\n  column1,\n  MAX(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 groups rows by the values in the column1<\/code> into groups.<\/li>\n\n\n\n
  2. The MAX<\/code> function returns the maximum value in the column2<\/code> for each group.<\/li>\n<\/ol>\n\n\n\n
    This query allows you to gain insights from the data by analyzing groups and finding the highest value for each.<\/p>\n\n\n\n
    Using MAX with Expressions #<\/a><\/h3>\n\n\n\n
    Besides a table column, you can use an expression in the MAX<\/code> function:<\/p>\n\n\n
    SELECT<\/span>\n  MAX(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<\/code> may involve table columns with operators.<\/p>\n\n\n\n
    PostgreSQL MAX Aggregate Function Examples #<\/a><\/h2>\n\n\n\n
    Let’s explore some examples of using the MAX<\/code> aggregate function.<\/p>\n\n\n\n
    Finding the Highest Price of All Products #<\/a><\/h3>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    The following example uses the MAX<\/code> function to find the highest price of all products:<\/p>\n\n\n
    SELECT<\/span>\n  MAX(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:<\/p>\n\n\n
       max\n---------<\/span>\n 2999.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 MAX<\/code> function examines all values in the price<\/code> column of the products<\/code> table and returns the highest one.<\/p>\n\n\n\n
    SELECT<\/span>\n  product_name,\n  price\nFROM<\/span>\n  products\nWHERE<\/span>\n  price = (\n    SELECT<\/span>\n      MAX(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:<\/p>\n\n\n
          product_name       |  price\n-------------------------+---------<\/span>\n Samsung QN900C Neo QLED | 2999.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
    To find the product with the highest price, you can use a subquery<\/a>:<\/p>\n\n\n
    Try it<\/a><\/p>\n\n\n\n
It’s important to note that the MAX<\/code> function ignores NULL<\/a><\/code> when determining the maximum value.<\/p>\n\n\n\n