exexe exexe - 2 months ago 11
SQL Question

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).

Answer
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