How to Find Minimum Values in Columns

Problem:

You want to find rows that store the smallest numeric value in a column.

Example:

Our database has a table named weather with data in the following columns: id, city, and temperature.

Here’s how to find cities with the lowest temperature.

Solution:

SELECT  id, city, temperature
FROM weather
WHERE temperature = (SELECT MIN(temperature) FROM weather);

Here’s the result:

Discussion:

To find the minimum value of a column, use the MIN() aggregate function; it takes the name of the column or expression to find the minimum value. In our example, the subquery returns the minimum value in the temperature column (subquery: SELECT MIN(temperature) FROM weather). The main query displays id, city, and temperature. To display only rows with minimal values in the column, use WHERE with a subquery (e.g., SELECT MIN(temperature) FROM weather). In the WHERE clause, place the column name with the relative value to be compared to the value returned by aggregate function in the subquery (WHERE temperature = (SELECT MIN(temperature) FROM weather)).