SQL Server selecting minimum of multiple columns

68de4m5k  于 2023-03-11  发布在  其他
关注(0)|答案(4)|浏览(122)

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.

bfhwhh0e

bfhwhh0e1#

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

odopli94

odopli942#

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;
jutyujz0

jutyujz03#

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
t1qtbnec

t1qtbnec4#

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

相关问题