MySQL - choosing the lowest value

Question

My database structure contains columns: id, name, value, dealer . I want to get the row with the lowest value for each dealer . I tried to get confused with MIN() and GROUP BY , still without a 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 well known, so there is a special page in the Mysql manual.

Check this out: Rows holding the group maximum / minimum of a specific column

Answer 2

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

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

Answer 3

 SELECT MIN(value),dealer FROM table_name GROUP BY dealer; 

Answer 4

Try the following:

 SELECT dealer, MIN(value) as "Lowest value" FROM value GROUP BY dealer; 

Answer 5

First you need to decide the lowest value for each dealer, and then get the rows that have this value for a particular dealer. I would do this:

 SELECT a.* FROM your_table AS a JOIN (SELECT dealer, Min(value) AS m FROM your_table GROUP BY dealer) AS b ON ( a.dealer= b.dealer AND a.value = bm ) 

Answer 6

 select id, name, value, dealer from yourtable where dealer in(select min(dealer) from yourtable group by name, value)