In PostgreSQL, finding the minimum or maximum value from a column is a common task. The aggregate functions like MAX() and MIN() are used to get the greatest or least value from a column. However, finding a row that keeps the maximum value in a given column is a real deal. To do this, the subquery can be used with a WHERE clause.

This blog will demonstrate the complete process of finding a row having a maximum value in a given column.

Find Maximum Values From a Row in Postgres

Use the MAX() function along with a subquery to find a row having a maximum value in a given column:

SELECT col_list
FROM tab_name
WHERE col_name = (SELECT MAX(col_name) FROM tab_name);

Here in this syntax, the WHERE clause with a subquery is utilized to show only those rows that have maximum values in the given column.

Example: How to Get a Row Having a Maximum Value

We have already created a sample table named “product_details” whose data is enlisted in the following snippet using the SELECT query:

SELECT * FROM product_details;

Suppose we want to check which product has the maximum quantity left. For this purpose, we will execute the MAX() function with a subquery as follows:

SELECT pro_id, pro_name, pro_price, pro_quantity
FROM product_details
WHERE pro_quantity = ( SELECT 
MAX(pro_quantity) 
FROM product_details);

In the above query:

- A SELECT query is used to fetch all columns of the “product_details” table.
- A subquery is created within the WHERE clause to get only those rows that have the maximum/highest quantity.
- Within the subquery, the MAX() function is applied to the “pro_quantity” column to get the rows with maximum quantity.

The output shows that the rows with the maximum quantity have been successfully retrieved.

That’s all about finding the rows having maximum values in a specific column.

Conclusion

Use the MAX() function along with a subquery to find a row having a maximum value in a given column. A subquery will retrieve only those rows that have the maximum numeric values. Moreover, users must use the subquery along with the WHERE clause to get the filtered result set. This blog post has presented a detailed understanding of getting the rows having maximum values in a specific column.