How to select data in MySQL where a field has a minimum value?

To select data where a field has min value, you can use aggregate function min(). The syntax is as follows.

SELECT *FROM yourTableName WHERE yourColumnName=(SELECT MIN(yourColumnName) FROM yourTableName);

To understand the above syntax, let us create a table. The query to create a table is as follows.

mysql> create table MinValueDemo
-> (
-> ProductId int,
-> ProductName varchar(100),
-> ProductPrice int
-> );
Query OK, 0 rows affected (0.77 sec)

Insert some records in the table using insert command. The query is as follows.

mysql> insert into MinValueDemo values(1,'product-1',4500);
Query OK, 1 row affected (0.14 sec)

mysql> insert into MinValueDemo values(2,'product-2',4340);
Query OK, 1 row affected (0.22 sec)

mysql> insert into MinValueDemo values(3,'product-3',4110);
Query OK, 1 row affected (0.18 sec)

mysql> insert into MinValueDemo values(4,'product-4',4344);
Query OK, 1 row affected (0.16 sec)

mysql> insert into MinValueDemo values(5,'product-5',4103);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement. The query is as follows.

mysql> select *from MinValueDemo;

The following is the output.

+-----------+-------------+--------------+
| ProductId | ProductName | ProductPrice |
+-----------+-------------+--------------+
| 1         | product-1   | 4500         |
| 2         | product-2   | 4340         |
| 3         | product-3   | 4110         |
| 4         | product-4   | 4344         |
| 5         | product-5   | 4103         |
+-----------+-------------+--------------+
5 rows in set (0.00 sec)

Here is the query to select data where the ‘ProductPrice’ has the minimum value using aggregate function MIN() from MySQL.

mysql> select *from MinValueDemo
-> where ProductPrice=(select min(ProductPrice) from MinValueDemo);

The following is the output.

+-----------+-------------+--------------+
| ProductId | ProductName | ProductPrice |
+-----------+-------------+--------------+
| 5         | product-5   | 4103         |
+-----------+-------------+--------------+
1 row in set (0.08 sec)