How to Efficiently Retrieve the Minimum Value for Each Dealer in MySQL?

Retrieving Lowest Value Per Dealer in MySQL

For a database structure with columns id, name, value, and dealer, the question arises as to how to obtain rows with the lowest value for each individual dealer. While attempting solutions using MIN() and GROUP BY, no satisfactory outcome has been achieved.

Solution:

To resolve this issue, two approaches are available:

Solution 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

Solution 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

Note: Solution 2 is considerably faster than Solution 1.

For further details, refer to the MySQL manual:

https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_row-number

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