How to Find Rows with Minimum Value

Problem

You want to find rows which 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. You want 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. The function 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. The subquery is:

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. In our example it is:

WHERE temperature = (SELECT MIN(temperature) FROM weather)