How to Select Records with the Minimum Value in a Specific Field Using MySQL?

Retrieving Data with the Minimum Value in a Specific Field Using MySQL

The goal is to retrieve data from a MySQL table where a particular field holds the minimum value.

Failed Attempt:

The initial attempt:

<code class="sql">SELECT * FROM pieces WHERE MIN(price)</code>

yields incorrect results as it returns the entire table with the minimum price, not individual records with the minimum price.

Solution:

To correctly select data with the minimum value, use the following query:

<code class="sql">SELECT *
FROM pieces
WHERE price =  ( SELECT MIN(price) FROM pieces )</code>

This query:

1. Executes a subquery to find the minimum price in the price field using MIN(price).
2. Compares the price field of each record in the pieces table to the minimum value obtained in step 1.
3. Returns only the records where the price matches the minimum value.

Example:

Consider the following table:

id	name	price
1	Apple	100
2	Banana	50
3	Cherry	75

Executing the query for this table would return the following result:

id	name	price
2	Banana	50

This result set shows that the record with id = 2 (Banana) has the minimum price of 50.

The above is the detailed content of How to Select Records with the Minimum Value in a Specific Field Using MySQL?. For more information, please follow other related articles on the PHP Chinese website!