selecting minimum of multiple columns

Question

I have three decimal columns named list1,list2,list3. I want to find the minimum of three in a single query.
I've tired this:

SELECT Least(list1, list2, list3) 
FROM   table1

It throws an error that least is not recognized function.

possible duplicate of [What's the best way to select the minimum value from multiple columns?](http://stackoverflow.com/questions/368351/whats-the-best-way-to-select-the-minimum-value-from-multiple-columns) — Salman A, Mar 02 '15 at 12:40

John Woo · Accepted Answer · 2012-10-18T08:50:17.967

7

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

edited Oct 18 '12 at 08:50

answered Oct 18 '12 at 06:24

John Woo

258,903
69
498
492

i have one doubt, is least is function of sql right, then why it says that it is not a inbuilt function – SRIRAM Oct 18 '12 at 06:30
No, not all RDBMS like yours supports `LEAST` function. – John Woo Oct 18 '12 at 06:33
@Maddy what are talking about? – John Woo Oct 18 '12 at 06:54
1

I have to side with @Maddy here, taking the least of 3 columns should not be done with the whole table, not the way the question was described. The least should only affect each row. I don't see how this solve the problem. – t-clausen.dk Oct 18 '12 at 08:26
1

This will work it the table has a primary key or unique id. Take a look at Maddy's and my scripts, Those doesn't rely on primary keys and is slightly faster than your script. – t-clausen.dk Oct 18 '12 at 09:25

score 4 · Answer 2 · answered Oct 18 '12 at 06:42

4

other solution,

case when col1 < col2 
     then case when col1 < col3 
               then col1
               else col3 end
     else case when col2 < col3
               then col2
               else col3 end;

answered Oct 18 '12 at 06:42

Maddy

3,726
9
41
55

1

this will work well with 3 columns, but with more than 3 it gets complicated. However i agree that it is a fast solution – t-clausen.dk Oct 18 '12 at 08:23
@t-clausen.dk, You are write this might not be the best approch when there are more columns for comarison – Maddy Oct 19 '12 at 04:31

score 1 · Answer 3 · answered Oct 18 '12 at 08:18

1

This will work if there is more than 1 row in the table

select c.mlist from table1 a
cross apply
(
select min(list1) mlist from
  (
    select list1
    union all
    select list2
    union all
    select list3
  ) b
) c

answered Oct 18 '12 at 08:18

t-clausen.dk

43,517
12
59
92

score 0 · Answer 4 · answered Mar 08 '23 at 20:47

As of SQL Server 2022 (and Azure SQL Database), you can use LEAST() function.

SELECT LEAST('6.62', 3.1415, N'7') AS LeastVal;
SELECT LEAST('Glacier', N'Joshua Tree', 'Mount Rainier') AS LeastString;

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-least-transact-sql?view=azure-sqldw-latest

selecting minimum of multiple columns

4 Answers4

SQLFiddle Demo