Select Minimum value and their entire column in mysql

Question

Consider below two table,

tab1:-

**id**   **name** 
  1     aaa
  2     bbb
  3     ccc

tab2:-

**id** **reference_id** **value**  **category**
 1               2         0.5          5
 2               2         0.03         9
 3               3         0.9          8
 4               1         0.3          3
 5               1         0.1          2

In tab2 the id from tab1 (reference_id) have 2 values and so on, I need the minimum value of each id, below is my tried code SELECT tab1.name,tab1.id,MIN(tab2.value) as VAL,tab2.category FROM tab1 JOIN tab2 ON tab1.id=tab2.reference_id WHERE 1 GROUP BY tab1.id I get below output

**name** **id** **VAL** **category**
  aaa       1     0.1       3
  bbb       2     0.03      5
  ccc       3     0.9       8

It seems minimum value correct but other column are shows wrong value , I need output like below ,

**name** **id** **VAL** **category**
  aaa       1     0.1       2
  bbb       2     0.03      9
  ccc       3     0.9       8

AnyHelp Appreciated

The duplicate answer works just as well for MIN as it does for MAX. — O. Jones, Apr 24 '18 at 10:53

score 1 · Accepted Answer · answered Apr 24 '18 at 10:55

You should use some nested subquery

  SELECT tab1.name
      ,tab1.id
      , t2.VAL
      ,t2.category 
  FROM tab1 
  JOIN (   select reference_id, val, category 
from tab2 
inner join (
      SELECT 
          ,tab2.reference_id 
          ,MIN(tab2.value) as VAL
      FROM  tab2
      group by tab2.reference_id 
) t on t.reference_id = tab2.reference_id and t.val = tab2.value 
) t2 on tab1.id = t2.reference_id

Select Minimum value and their entire column in mysql

1 Answers1