MySQL - Fetching lowest value

Question

My database structure contains columns: id, name, value, dealer. I want to retrieve row with lowest value for each dealer. I've been trying to mess up with MIN() and GROUP BY, still - no solution.

Answer 1

Solution1:

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

Solution2:

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 very famous, so there is a special page for this in Mysql's manual.

Check this: Rows Holding the Group-wise Maximum/Minimum of a Certain Column

Answer 2

select id,name,MIN(value) as pkvalue,dealer from TABLENAME 
group by id,name,dealer;

here you group all rows by id,name,dealer and then you will get min value as pkvalue.