Back to cookbooks list Articles Cookbook

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.

idcitytemperature
1Houston23
2Atlanta20
3Boston15
4Cleveland15
5Dallas34
6Austin28

Solution:

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

Here’s the result:

idcitytemperature
3Boston15
4Cleveland15

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)

Recommended courses:

Recommended articles:

See also: