Fetching minimum value based on two column in Mysql

Question

I have a table which look like:

ID     Fig1     Fig2
A-1    10       -18
A-1    15        20
A-1    25        75
A-2    18        22
A-2    34        70

Now I want to fetch minimum value against each unique id by considering Fig1 and Fig2.

Output Would be:

ID     Min Value
A-1   -18
A-2    18

Answer 1

Simply UNION the columns and find the MINIMUM value.

Try this:

SELECT ID, MIN(D.Fig1) MinValue
FROM(
    SELECT ID,Fig1 FROM YourTable
    UNION
    SELECT ID,Fig2 FROM YourTable
)D
GROUP BY ID

Answer 2

Try this:

SELECT
    ID, 
    MIN(LEAST(Fig1, Fig2)) AS Min_value
FROM yourTable
GROUP BY ID

Answer 3

Using Simple CASE When and Min Fucntion statment :

        Select ID,
        CASE WHEN min(Fig1)>min(FIG2) Then min(FIG2)
        WHEN min(Fig1)<min(FIG2) Then min(FIG1)
        Else NULL End AS FIG
        from 
        #TABLE 
        Group by ID

OutPut :

Answer 4

maybe duplicate of MySQL Select minimum/maximum among two (or more) given values

I suppose you are looking for:

GREATEST()

and

LEAST()

here u'll find a working fiddle: http://www.sqlfiddle.com/#!9/a762df/2

the resulting query would be

select ID, LEAST(MIN(Fig1),MIN(Fig2)) from dummy group by ID

Answer 5

Try this answer:

SELECT ID, LEAST(Fig1, Fig2) AS Min_value 
FROM testTable 
GROUP BY ID