{"id":724,"date":"2024-12-07T21:01:18","date_gmt":"2024-12-07T14:01:18","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=724"},"modified":"2025-01-03T16:10:18","modified_gmt":"2025-01-03T09:10:18","slug":"postgresql-greatest","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-greatest\/","title":{"rendered":"PostgreSQL GREATEST Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL GREATEST<\/code> function to select the highest value from a list of values.<\/p>\n\n\n\n

Getting Started with the PostgreSQL GREATEST function #<\/a><\/h2>\n\n\n\n

The GREATEST<\/code> function accepts a list of values and returns the largest value.<\/p>\n\n\n\n

Here’s the syntax of the GREATEST<\/code> function:<\/p>\n\n\n

GREATEST (expression1, expression2, ....)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In SQL standard, the GREATEST<\/code> function returns NULL<\/code> if any expression evaluates to NULL<\/code>. Some databases behave this way like MySQL<\/a>.<\/p>\n\n\n\n
Basic PostgreSQL GREATEST function examples #<\/a><\/h2>\n\n\n\n
The following example uses the GREATEST<\/code> function to return the highest number in the list:<\/p>\n\n\n
SELECT<\/span> GREATEST(1<\/span>,2<\/span>,3<\/span>) highest;<\/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
 highest\n---------\n       3<\/code><\/span><\/pre>\n\n\n
The GREATEST<\/code> function returns 3 because it is the highest number in the list.<\/p>\n\n\n\n
The following example uses the GREATEST<\/code> function to return the largest string in alphabetical order:<\/p>\n\n\n
SELECT<\/span> GREATEST('A'<\/span>,'B'<\/span>,'C'<\/span>) largest;<\/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
 largest\n---------\n C<\/code><\/span><\/pre>\n\n\n
The GREATEST<\/code> function returns the letter 'C'<\/code> is the largest value alphabetically among  'A'<\/code>, 'B'<\/code>, and 'C'<\/code>.<\/p>\n\n\n\n
The following statement uses the GREATEST<\/code> function to compare two boolean<\/a> values:<\/p>\n\n\n
SELECT<\/span> GREATEST(true<\/span>, false<\/span>) largest;<\/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
 largest\n---------\n t<\/code><\/span><\/pre>\n\n\n
When comparing true<\/code> and false<\/code>, true<\/code> is greater than false<\/code> so the GREATEST<\/code> function returns true<\/code>.<\/p>\n\n\n\n
The following example uses the GREATEST<\/code> function to find the latest date in a list of date literals:<\/p>\n\n\n
SELECT<\/span>\n  GREATEST ('2025-01-01'<\/span>, '2025-02-01'<\/span>, '2025-03-01'<\/span>, NULL<\/span>) latest;<\/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
   latest\n------------\n 2025-03-01<\/code><\/span><\/pre>\n\n\n
In this example, the GREATEST<\/code> function returns the March 1, 2025<\/code> because it the latest dates among January 1, 2025<\/code>, February 1 2025<\/code>, and March 1, 2025<\/code><\/p>\n\n\n\n
Notice that The GREATEST<\/code> function ignores NULL<\/code> in this example.<\/p>\n\n\n\n
Using the PostgreSQL GREATEST function with table data #<\/a><\/h2>\n\n\n\n
We’ll use the products<\/code> table from the inventory<\/code> database.<\/p>\n\n\n\n
Assuming that the shipping cost is calculated as follows:<\/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
Try it<\/a><\/p>\n\n\n\n
The GREATEST<\/code> function ignores NULL<\/a><\/code>. It’ll return NULL<\/code> if all expressions evaluate to NULL<\/code>.<\/p>\n\n\n\n