CODEWITHSUNDEEP
mysqlsql
exexe
exexe

Reputation: 196

Select multiple columns with min value

Example schema:

id val1 val2 val3 val4 val5 mid
1   5     6    7    1    4   1
4   7     8    5    9    5   1
5   4     1    4    2    7   2
7   3     2    8    7    2   1

Here is what im trying, but this query returns only 1 row with all values:

SELECT id, MIN(val1), MIN(val2), MIN(val3), MIN(val4), MIN(val5) FROM table WHERE mid=1

I want results to be (for each val column select id,min as min(valx) where mid=1):

id min
7   3
7   2
4   5
1   1
7   2

One note: amount of columns can be up to 50 (valx where x is number from 1 to 50).

Upvotes: 1

Views: 3603

Answers (6)

Ahmed Saeed
Ahmed Saeed

Reputation: 851

Sorry for the previous answer, didn't see your result. I hope this will give you the result.

;with cte as(
    Select a.ID
        , t.Val
        , ROW_NUMBER() over(partition by a.id order by t.ColNo) as ColNo
    From @TABLE as a
    Outer apply (Values (1,Val1), (2,Val2),(3,Val3),(4,Val4), (5,Val5) ) t(ColNo,Val)
    Where a.MID=1
)
, cte2 as( 
    Select ColNo, MIN(val) as MinVal 
    From cte
    Group by ColNo
) 
    Select a.ID, a.Val from cte as a
    Inner join cte2 as b on a.ColNo=b.ColNo and a.Val=b.MinVal
    Order by a.ColNo

Upvotes: 0

Jayvee
Jayvee

Reputation: 10875

select id, val1 from table where val1=(select min(val1) from table where mid=1) and mid=1
union all
select id, val2 from table where val2=(select min(val2) from table where mid=1) and mid=1
union all
select id, val3 from table where val3=(select min(val3) from table where mid=1) and mid=1
union all
select id, val4 from table where val4=(select min(val4) from table where mid=1) and mid=1
union all
select id, val5 from table where val5=(select min(val5) from table where mid=1) and mid=1

Upvotes: 1

Matt
Matt

Reputation: 14341

SELECT id, minimum
FROM
    (SELECT MIN(Val1) as minimum FROM TableName WHERE Mid = 1) v1
    INNER JOIN TableName t1
    ON v1.minimum = t1.Val1
    AND t1.Mid = 1

UNION ALL

SELECT id, minimum
FROM
    (SELECT MIN(Val2) as minimum FROM TableName WHERE Mid = 1) v2
    INNER JOIN TableName t2
    ON v2.minimum = t2.Val2
    AND t2.Mid = 1

UNION ALL

SELECT id, minimum
FROM
    (SELECT MIN(Val3) as minimum FROM TableName WHERE Mid = 1) v3
    INNER JOIN TableName t3
    ON v3.minimum = t3.Val3
    AND t3.Mid = 1

UNION ALL

SELECT id, minimum
FROM
    (SELECT MIN(Val4) as minimum FROM TableName WHERE Mid = 1) v4
    INNER JOIN TableName t4
    ON v4.minimum = t4.Val4
    AND t4.Mid = 1

UNION ALL

SELECT id, minimum
FROM
    (SELECT MIN(Val5) as minimum FROM TableName WHERE Mid = 1) v5
    INNER JOIN TableName t5
    ON v5.minimum = t5.Val5
    AND t5.Mid = 1

This is just a nasty nasty query and situation but I figured out what you want. If you improve your narrative you might be get some better answers than this one.

Here is what I took your desired results and narrative to mean.

Find the Minimum value of each column where Mid = 1, then Find the ID(s) that relate to that minimum value. The results of above are:

id  minimum
7   3
7   2
4   5
1   1
7   2

Note the 4 5 pair you actually have 5 4 but 5 would be the minimum value in Val3 because 4 represented is where mid = 2. So the ID for value 5 is 4....

Upvotes: 1

Asifuzzaman
Asifuzzaman

Reputation: 1783

If I understand your question correctly I hope It will help you

 SELECT id, MIN(minimum) as minimum FROM (
 SELECT id, val1 As minimum FROM table where  mid=1
 UNION ALL
 SELECT id, val2 As minimum FROM table where  mid=1
 UNION ALL
 SELECT id, val3 As minimum FROM table where  mid=1
 union All
 SELECT id, val4 As minimum FROM table  where  mid=1
 union ALL
 SELECT id, val5 As minimum FROM table where  mid=1
 ) As minvalue
 GROUP BY id

Upvotes: 0

Ahmed Saeed
Ahmed Saeed

Reputation: 851

SELECT id, MIN(val1), MIN(val2), MIN(val3), MIN(val4), MIN(val5) 
FROM table 
WHERE mid=1 
GROUP by id

Upvotes: -1

JohnHC
JohnHC

Reputation: 11195

Use the LEAST() function...

select id, least(val1, val2, <etc>)
from myTable
where mid=1

Upvotes: 0

Related Questions