Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in PostgreSQL

Introduction
The COALESCE() function
The GREATEST() & LEAST() functions
9. The GREATEST() function
The NULLIF() function
Summary

Instruction

Great! The next function you'll get to know is GREATEST(), used to select the largest value among values given as arguments. Look at the first example of the function GREATEST():

SELECT
  id,
  GREATEST(market1_price, market2_price) AS largest_price
FROM product;

How does it work? In our database, each product has three different prices in three different shops (columns: market1_price, market2_price, market3_price). For each product, the query above displays, the largest price between shop 1 and shop 2.

Don't consider using the MAX() function, as it's a not good solution. Why? The MAX() function is an aggregate function. It returns only one value – the greatest value in a column and it collapses all the rows. The GREATEST() function finds the largest value from a list of expressions. It can select a different value for each row.

Exercise

Find the largest price for each product among all three shops. Select ID, the name of the product and the largest price as the largest_price column.

Stuck? Here's a hint!

Use this expression:

GREATEST(market1_price, market2_price, market3_price) AS largest_price