CODEWITHSUNDEEP
mysqlsql
Vector JX
Vector JX

Reputation: 179

Fetching minimum value based on two column in Mysql

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

Upvotes: 0

Views: 160

Answers (5)

Pooja Rani
Pooja Rani

Reputation: 11

Try this answer:

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

Upvotes: 1

Emanuel Jung
Emanuel Jung

Reputation: 11

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

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

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 :

enter image description here

Upvotes: 1

kc2018
kc2018

Reputation: 1460

Try this:

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

Upvotes: 2

DineshDB
DineshDB

Reputation: 6193

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

Upvotes: 2

Related Questions