What's the Best Way to Select the Minimum Value from Several Columns

What's the best way to select the minimum value from several columns?

There are likely to be many ways to accomplish this. My suggestion is to use Case/When to do it. With 3 columns, it's not too bad.

Select Id,
       Case When Col1 < Col2 And Col1 < Col3 Then Col1
            When Col2 < Col1 And Col2 < Col3 Then Col2 
            Else Col3
            End As TheMin
From   YourTableNameHere

selecting minimum of multiple columns

Try using UNION

SELECT MIN(x.a)
FROM
    (
        SELECT list1 a FROM table1
        UNION
        SELECT list2 a FROM table1
        UNION
        SELECT list3 a FROM table1
    ) x

UPDATE 1

SELECT ID,MIN(x.a)
FROM
    (
        SELECT ID,list1 a FROM table1
        UNION
        SELECT ID,list2 a FROM table1
        UNION
        SELECT ID,list3 a FROM table1
    ) x
GROUP BY ID

SQLFiddle Demo

Get the minimum value between several columns

There is no built in function to return the min/max of two (or more) columns. You could implement your own scalar function to do this.

In SQL Server 2005+ you could use UNPIVOT to turn the columns into rows and then use the MIN function:

CREATE TABLE [X]
(
    [ID] INT,
    [Date1] DATETIME,
    [Date2] DATETIME,
    [Date3] DATETIME
)

INSERT  [X]
VALUES  (0, '09/29/2011', '09/20/2011', '09/01/2011'),
        (1, '01/01/2011', '01/05/2011', '03/03/2010')


SELECT [ID], MIN([Date]) AS [MinDate]
FROM [X]
UNPIVOT (
    [Date] FOR d IN
        ([Date1]
        ,[Date2]
        ,[Date3])
) unpvt
GROUP BY [ID]

pandas get the row-wise minimum value of two or more columns

If you are trying to get the row-wise mininum of two or more columns, use pandas.DataFrame.min and specify axis=1.

data['min_c_h'] = data[['flow_h','flow_c']].min(axis=1)

# display(data)
   flow_c  flow_d  flow_h  min_c_h
0      82      36      43       43
1      52      48      12       12
2      33      28      77       33
3      91      99      11       11
4      44      95      27       27
5       5      94      64        5
6      98       3      88       88
7      73      39      92       73
8      26      39      62       26
9      56      74      50       50

Finding minimum by groups and among columns

We can use data.table methods

library(data.table)
setDT(df)[df[, .I[which.min(do.call(pmin, .SD))], 
     group, .SDcols = patterns('^group_score')]$V1]
#   group cut group_score_1 group_score_2
#1:     a   0             2           2.5
#2:     b   1             5           1.0

select minimum value from three different columns oracle

Not MIN, but LEAST:

least(min_i_start, min_lg_start, min_af_start)

Select multiple columns with min value

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

---

Related Topics