exexe - 11 months ago 57

SQL Question

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 Source

```
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
```