Articles Cookbook
Back to list
Standard SQL

How to Find Minimum Values in Columns

Database:

Operators:

MIN, WHERE

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.

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

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:

idcitytemperature
3Boston15
4Cleveland15

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)).

Recommended courses:

Recommended articles:

See also:

go to top