How to Retrieve the Row with the Lowest Value for Each Dealer in MySQL?

Retrieving Lowest Values for Each Dealer in MySQL

Problem

In a database containing a table with columns id, name, value, and dealer, the task is to fetch the row with the lowest value for each unique dealer.

Solution

Method 1:

SELECT t1.*
FROM your_table t1
JOIN (
  SELECT MIN(value) AS min_value, dealer
  FROM your_table
  GROUP BY dealer
) AS t2 ON t1.dealer = t2.dealer AND t1.value = t2.min_value;

Method 2 (Recommended):

SELECT t1.*
FROM your_table t1
LEFT JOIN your_table t2
ON t1.dealer = t2.dealer AND t1.value > t2.value
WHERE t2.value IS NULL;

This problem is commonly known as the "Row Holding the Group-wise Maximum/Minimum of a Certain Column." The second solution is generally more efficient than the first.

For further information, refer to the corresponding page in the MySQL manual: https://dev.mysql.com/doc/refman/8.0/en/group-by-rows-max-min.html

The above is the detailed content of How to Retrieve the Row with the Lowest Value for Each Dealer in MySQL?. For more information, please follow other related articles on the PHP Chinese website!