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

Question

Given the following table in SQL Server 2005:

ID   Col1   Col2   Col3
--   ----   ----   ----
1       3     34     76  
2      32    976     24
3       7    235      3
4     245      1    792

What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium values out of Col1, Col2, and Col 3 for each row)?

ID   Col1   Col2   Col3  TheMin
--   ----   ----   ----  ------
1       3     34     76       3
2      32    976     24      24
3       7    235      3       3
4     245      1    792       1

UPDATE:

For clarification (as I have said in the coments) in the real scenario the database is properly normalized. These "array" columns are not in an actual table but are in a result set that is required in a report. And the new requirement is that the report also needs this MinValue column. I can't change the underlying result set and therefore I was looking to T-SQL for a handy "get out of jail card".

I tried the CASE approach mentioned below and it works, although it is a bit cumbersome. It is also more complicated than stated in the answers because you need to cater for the fact that there are two min values in the same row.

Anyway, I thought I'd post my current solution which, given my constraints, works pretty well. It uses the UNPIVOT operator:

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from 
        cte 
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

I'll say upfront that I don't expect this to offer the best performance, but given the circumstances (I can't redesign all the queries just for the new MinValue column requirement), it is a pretty elegant "get out of jail card".

Bowlin

IMHO the author's UNPIVOT solution is superior to the other answers. – Curable

I find Nizam's solution to be the leanest solution, even if it took me a while to start understanding it. Lean and very usable. – Rajah

Answer 1

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

Guzman

This was my initial thought. But the real query needs 5 columns, and the number of columns could grow. So the CASE approach becomes a little unwieldy. But it does work. – Bowlin

If the number of columns could grow, you're definitely doing it wrong - see my post (the rant on why you shouldn't have your DB schema set up this way :-). – Ryun

Thanks. As I mentioned in another comment. I'm not querying actual tables. The tables are normalised correctly. This query is part of a particularly complex query and is working on intermediate results from derived tables. – Bowlin

In that case, can you derive them differently so they look normalized? – Morez

I'd be very circumspect about using this on any decent-sized table. SELECTs that perform functions on every row are notoriously unscalable. Assuming your row count is small enough, this won't matter but it will cause troubles on large databases. – Ryun

An add on to the answer from @Gmastros as I ran into the issue that some of the Cols had matching data so I had to add the = sign. My data also had the potential for null so I had to add in the or statement to account for that. There maybe an easier way to do this but I have not found one in the past 6 months I have been looking. Thanks to everyone involved here. Select Id, CaseWhen (Col1 <= Col2 OR Col2 is null) And (Col1 <= Col3 OR Col3 is null) Then Col1 When (Col2 <= Col1 OR Col1 is null) And (Col2 <= Col3 OR Col3 is null) Then Col2 Else Col3 End As TheMin From YourTableNameHere – Laspisa

This answer is wrong, as it doesn't take account of nulls or columns with equal values. See the comment from @ChadPortman – Pisano

Answer 2

Using CROSS APPLY:

SELECT ID, Col1, Col2, Col3, MinValue
FROM YourTable
CROSS APPLY (SELECT MIN(d) AS MinValue FROM (VALUES (Col1), (Col2), (Col3)) AS a(d)) A

SQL Fiddle

Carping

Looks interesting but I can't get this to work. Could you perhaps explicit a bit ? thx – Rajah

@Rajah I inserted the SQL Fiddle in my answer – Carping

The thing I did not know was the scalar function. It seems that your answer works also without the cross apply. Does it add value/performance ? https://mcmap.net/q/189397/-get-the-minimum-value-between-several-columns – Rajah

@Rajah If it not delivers performance, it increases readbility. For example, I could use something like where MinValue > 10, which I could not do without CROSS APPLY – Carping

indeed, I had the opportunity to understand the "reusability" benefit of it in the meantime. Thanks. I learned 2 things today ;-) – Rajah

Thank you from 2017! I just need to adjust this a bit more. – Eastwards

@PatrickHonorez with CROSS APPLY you can reference the result inside the WHERE clause and afterwards (GROUP BY, HAVING, WINDOW, SELECT and ORDER BY...you can even reference it inside JOIN). If used inside SELECT you can access it inside ORDER BY. – Aluminiferous

This solution appears to be RDBMS dependent. For example, SQL Fiddle works for MS SQL, but fails for all others - MySQL, Oracle, PostgreSQL, SQLite. I also tested on my GBQ instance and it fails there as well – Cervantes

Answer 3

SELECT ID, Col1, Col2, Col3, 
    (SELECT MIN(Col) FROM (VALUES (Col1), (Col2), (Col3)) AS X(Col)) AS TheMin
FROM Table

The OP was tagged as sql-server, and this is an SQL Server specific answer. May not work on other DBMSs. (Search for least for Postgres derived DBs and some others).

Crave

Thanks for the catch. I'd missed that tag. I actually don't know, and don't have the ability to test it either. Will be more dilligent in checking tags going forward. – Crave

Hands down the more elegant solution - not sure why it doesn't have more upvotes. – Midrash

For inline max/min calcs this is by far the best way to do this – Faris

In 2022, this solution doesn't work for Google BigQuery - perhaps that's also the case for other RDBMS. Error is 'Syntax error: Expected keyword JOIN but got ","' – Cervantes

@Cervantes The OP is tagged with sql-server, so the answer is sql-server. – Crave

Answer 4

On MySQL, use this:

select least(col1, col2, col3) FROM yourtable

Lambertson

Might not be an SQL statement. – Isopropyl

but in some cases it is. for those, this is a wonderful answer – Decomposition

Available in Postgres since 8.1: postgresql.org/docs/8.1/functions-conditional.html#AEN12704 – Godsend

This non-standard SQL extension is supported by pretty much every database except Microsoft SQL server. – Ametropia

LEAST works in the latest version of Microsoft SQL Server Managed Instances, as of ~12 days ago. reddit.com/r/SQLServer/comments/k0dj2r/… – Dais

Works in Informix as well, thank you for the answer. – Sparhawk

The question is about SQL Server which doesn't have this function. – Honest

For any readers using SQLLite, The min function works like least in SQLLite, but takes NULL if any value is NULL. See this related question. – Sibyl

The opposite is GREATEST – Clavius

Answer 5

As of SQL Server 2022, you can use the LEAST function for this. There is also the GREATEST function.

---

For earlier versions, you can use the "brute force" approach with a twist:

SELECT CASE
    WHEN Col1 <= Col2 AND Col1 <= Col3 THEN Col1
    WHEN                  Col2 <= Col3 THEN Col2
    ELSE                                    Col3
END AS [Min Value] FROM [Your Table]

When the first when condition fails it guarantees that Col1 is not the smallest value therefore you can eliminate it from rest of the conditions. Likewise for subsequent conditions. For five columns your query becomes:

SELECT CASE
    WHEN Col1 <= Col2 AND Col1 <= Col3 AND Col1 <= Col4 AND Col1 <= Col5 THEN Col1
    WHEN                  Col2 <= Col3 AND Col2 <= Col4 AND Col2 <= Col5 THEN Col2
    WHEN                                   Col3 <= Col4 AND Col3 <= Col5 THEN Col3
    WHEN                                                    Col4 <= Col5 THEN Col4
    ELSE                                                                      Col5
END AS [Min Value] FROM [Your Table]

Note that if there is a tie between two or more columns then <= ensures that we exit the CASE statement as early as possible.

Aluminiferous

Use <= instead, otherwise, the last matching min value will be used instead of the first. – Elfrieda

Answer 6

The best way to do that is probably not to do it - it's strange that people insist on storing their data in a way that requires SQL "gymnastics" to extract meaningful information, when there are far easier ways to achieve the desired result if you just structure your schema a little better :-)

The right way to do this, in my opinion, is to have the following table:

ID    Col    Val
--    ---    ---
 1      1      3
 1      2     34
 1      3     76

 2      1     32
 2      2    976
 2      3     24

 3      1      7
 3      2    235
 3      3      3

 4      1    245
 4      2      1
 4      3    792

with ID/Col as the primary key (and possibly Col as an extra key, depending on your needs). Then your query becomes a simple select min(val) from tbl and you can still treat the individual 'old columns' separately by using where col = 2 in your other queries. This also allows for easy expansion should the number of 'old columns' grow.

This makes your queries so much easier. The general guideline I tend to use is, if you ever have something that looks like an array in a database row, you're probably doing something wrong and should think about restructuring the data.

---

However, if for some reason you can't change those columns, I'd suggest using insert and update triggers and add another column which these triggers set to the minimum on Col1/2/3. This will move the 'cost' of the operation away from the select to the update/insert where it belongs - most database tables in my experience are read far more often than written so incurring the cost on write tends to be more efficient over time.

In other words, the minimum for a row only changes when one of the other columns change, so that's when you should be calculating it, not every time you select (which is wasted if the data isn't changing). You would then end up with a table like:

ID   Col1   Col2   Col3   MinVal
--   ----   ----   ----   ------
 1      3     34     76        3
 2     32    976     24       24
 3      7    235      3        3
 4    245      1    792        1

Any other option that has to make decisions at select time is usually a bad idea performance-wise, since the data only changes on insert/update - the addition of another column takes up more space in the DB and will be slightly slower for the inserts and updates but can be much faster for selects - the preferred approach should depend on your priorities there but, as stated, most tables are read far more often than they're written.

Ryun

Um. Thanks for the diatribe. The real database is properly normalized. This was a simple example. The actual query is complicated and the 5 columns I am interested in are intermediate results from derived tables. – Bowlin

The diatribe still stands unfortunately. Making intermediate tables of the form you suggest is every bit as problematic as making permanent tables like that. This is evidenced by the fact that you have to perform what I like to call SQL gymnastics to get the result you want. – Ryun

If there are genuine reasons for needing the 'array' in a single row, feel free to enlighten us, but using it to select the minimum value is not one of them. – Ryun

+1 for the trigger suggestion to preserve the original (if flawed) table structure. – Ceres

What if you are dealing with a Hierarchy table, joined to it self? – Tessler

Hi please explain how a table with one key and 5 numeric properties is not properly normalized. It seems to be exactly in the meet the definition through at least the third normal form. – Gasket

Answer 7

If the columns were integers as in your example I would create a function:

create function f_min_int(@a as int, @b as int) 
returns int
as
begin
    return case when @a < @b then @a else coalesce(@b,@a) end
end

then when I need to use it I would do :

select col1, col2, col3, dbo.f_min_int(dbo.f_min_int(col1,col2),col3)

if you have 5 colums then the above becomes

select col1, col2, col3, col4, col5,
dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(dbo.f_min_int(col1,col2),col3),col4),col5)

Virago

Given the ridiculously bad performance of scalar functions in MSSQL I'm feeling obliged to advice against this approach. If you'd go this road than at least write a function that takes all 5 columns as parameters at once. It's still going to be bad, but at least a bit less bad =/ – Inchworm

Recursion will reduce the performance. But it will meet the requirement. – Isopropyl

Answer 8

This is brute force but works

 select case when col1 <= col2 and col1 <= col3 then col1
           case when col2 <= col1 and col2 <= col3 then col2
           case when col3 <= col1 and col3 <= col2 then col3
    as 'TheMin'
           end

from Table T

... because min() works only on one column and not across columns.

Ileostomy

It also tends to be the fastest since MIN creates implicit nested loop join. – Dais

Answer 9

You could also do this with a union query. As the number of columns increase, you would need to modify the query, but at least it would be a straight forward modification.

Select T.Id, T.Col1, T.Col2, T.Col3, A.TheMin
From   YourTable T
       Inner Join (
         Select A.Id, Min(A.Col1) As TheMin
         From   (
                Select Id, Col1
                From   YourTable

                Union All

                Select Id, Col2
                From   YourTable

                Union All

                Select Id, Col3
                From   YourTable
                ) As A
         Group By A.Id
       ) As A
       On T.Id = A.Id

Guzman

This works but performance will degrade when the row count rises. – Retsina

Thanks. Yes this works. As Tomalak says, in my realword query this would be quite nasty for performance. But +1 for effort. :) – Bowlin

Answer 10

Both this question And this question try to answer this.

The recap is that Oracle has a built in function for this, with Sql Server you are stuck either defining a user-defined-function or using case statements.

Venetis

Answer 11

For multiple columns its best to use a CASE statement, however for two numeric columns i and j you can use simple math:

min(i,j) = (i+j)/2 - abs(i-j)/2

This formula can be used to get the minimum value of multiple columns but its really messy past 2, min(i,j,k) would be min(i,min(j,k))

Erective

Answer 12

If you're able to make a stored procedure, it could take an array of values, and you could just call that.

Morez

Oracle has a function called LEAST() that does exactly what you want. – Morez

Thanks for rubbing that in :) I can't believe that SQL Server doesn't have an equivalent! – Bowlin

I was even going to say, "Hey, my favourite pgsql doesn't have it either," but it actually does. ;) The function itself wouldn't be tough to write though. – Morez

Oh, except that T-SQL doesn't even have array support (???) Well, I guess you could have a five-parameter function and if you need more just extend it... – Morez

Answer 13

select *,
case when column1 < columnl2 And column1 < column3 then column1
when columnl2 < column1 And columnl2 < column3 then columnl2
else column3
end As minValue
from   tbl_example

Madsen

This is a duplicate of G Mastros' answer, so if you should wonder: I guess that's where down-vote comes from. – Retsina

Answer 14

If you use SQL 2005 you can do something neat like this:

;WITH    res
          AS ( SELECT   t.YourID ,
                        CAST(( SELECT   Col1 AS c01 ,
                                        Col2 AS c02 ,
                                        Col3 AS c03 ,
                                        Col4 AS c04 ,
                                        Col5 AS c05
                               FROM     YourTable AS cols
                               WHERE    YourID = t.YourID
                             FOR
                               XML AUTO ,
                                   ELEMENTS
                             ) AS XML) AS colslist
               FROM     YourTable AS t
             )
    SELECT  YourID ,
            colslist.query('for $c in //cols return min(data($c/*))').value('.',
                                            'real') AS YourMin ,
            colslist.query('for $c in //cols return avg(data($c/*))').value('.',
                                            'real') AS YourAvg ,
            colslist.query('for $c in //cols return max(data($c/*))').value('.',
                                            'real') AS YourMax
    FROM    res

This way you don't get lost in so many operators :)

However, this could be slower than the other choice.

It's your choice...

Lewin

Well, like I said, this could be slow, but if you have too many columns (obviously, as a result of a really bad DB design!), it could worth using this (at least for AVG). You didn't give me any hint if it's a good holy cow or a bad one:) Maybe you should use the up/down vote to help me figure out. – Lewin

It wasn't really a good or a bad one ;). I'm no database expert, so I was just saying "holy cow" because the question seemed like it would have a trivial answer. I guess it's a good one since you managed to provide a flexible, extensible solution to the problem! – Chinchin

Answer 15

A little twist on the union query:

DECLARE @Foo TABLE (ID INT, Col1 INT, Col2 INT, Col3 INT)

INSERT @Foo (ID, Col1, Col2, Col3)
VALUES
(1, 3, 34, 76),
(2, 32, 976, 24),
(3, 7, 235, 3),
(4, 245, 1, 792)

SELECT
    ID,
    Col1,
    Col2,
    Col3,
    (
        SELECT MIN(T.Col)
        FROM
        (
            SELECT Foo.Col1 AS Col UNION ALL
            SELECT Foo.Col2 AS Col UNION ALL
            SELECT Foo.Col3 AS Col 
        ) AS T
    ) AS TheMin
FROM
    @Foo AS Foo

Coolant

Answer 16

If you know what values you are looking for, usually a status code, the following can be helpful:

select case when 0 in (PAGE1STATUS ,PAGE2STATUS ,PAGE3STATUS,
PAGE4STATUS,PAGE5STATUS ,PAGE6STATUS) then 0 else 1 end
FROM CUSTOMERS_FORMS

Allotrope

Answer 17

Below I use a temp table to get the minimum of several dates. The first temp table queries several joined tables to get various dates (as well as other values for the query), the second temp table then gets the various columns and the minimum date using as many passes as there are date columns.

This is essentially like the union query, the same number of passes are required, but may be more efficient (based on experience, but would need testing). Efficiency wasn't an issue in this case (8,000 records). One could index etc.

--==================== this gets minimums and global min
if object_id('tempdb..#temp1') is not null
    drop table #temp1
if object_id('tempdb..#temp2') is not null
    drop table #temp2

select r.recordid ,  r.ReferenceNumber, i.InventionTitle, RecordDate, i.ReceivedDate
, min(fi.uploaddate) [Min File Upload], min(fi.CorrespondenceDate) [Min File Correspondence]
into #temp1
from record r 
join Invention i on i.inventionid = r.recordid
left join LnkRecordFile lrf on lrf.recordid = r.recordid
left join fileinformation fi on fi.fileid = lrf.fileid
where r.recorddate > '2015-05-26'
 group by  r.recordid, recorddate, i.ReceivedDate,
 r.ReferenceNumber, i.InventionTitle



select recordid, recorddate [min date]
into #temp2
from #temp1

update #temp2
set [min date] = ReceivedDate 
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.ReceivedDate < [min date] and  t1.ReceivedDate > '2001-01-01'

update #temp2 
set [min date] = t1.[Min File Upload]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Upload] < [min date] and  t1.[Min File Upload] > '2001-01-01'

update #temp2
set [min date] = t1.[Min File Correspondence]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
where t1.[Min File Correspondence] < [min date] and t1.[Min File Correspondence] > '2001-01-01'


select t1.*, t2.[min date] [LOWEST DATE]
from #temp1 t1 join #temp2 t2 on t1.recordid = t2.recordid
order by t1.recordid

Ovi

Answer 18

SELECT [ID],
            (
                SELECT MIN([value].[MinValue])
                FROM
                (
                    VALUES
                        ([Col1]),
                        ([Col1]),
                        ([Col2]),
                        ([Col3])
                ) AS [value] ([MinValue])
           ) AS [MinValue]
FROM Table;

Isopropyl

Answer 19

I know that question is old, but I was still in the need of the answer and was not happy with other answers so I had to devise my own which is a twist on @paxdiablo´s answer.

---

I came from land of SAP ASE 16.0, and I only needed a peek at statistics of certain data which are IMHO validly stored in different columns of a single row (they represent different times - when arrival of something was planned, what it was expected when the action started and finally what was the actual time). Thus I had transposed columns into the rows of temporary table and preformed my query over this as usually.

N.B. Not the one-size-fits-all solution ahead!

CREATE TABLE #tempTable (ID int, columnName varchar(20), dataValue int)

INSERT INTO #tempTable 
  SELECT ID, 'Col1', Col1
    FROM sourceTable
   WHERE Col1 IS NOT NULL
INSERT INTO #tempTable 
  SELECT ID, 'Col2', Col2
    FROM sourceTable
   WHERE Col2 IS NOT NULL
INSERT INTO #tempTable 
  SELECT ID, 'Col3', Col3
    FROM sourceTable
   WHERE Col3 IS NOT NULL

SELECT ID
     , min(dataValue) AS 'Min'
     , max(dataValue) AS 'Max'
     , max(dataValue) - min(dataValue) AS 'Diff' 
  FROM #tempTable 
  GROUP BY ID

This took some 30 seconds on source set of 630000 rows and used only index-data, so not the thing to run in time-critical process but for things like one-time data inspection or end-of-the-day report you might be fine (but verify this with your peers or superiors, please!). Main bonus of this style for me was that I could readily use more/less columns and change grouping, filtering, etc., especially once data was copyied over.

The additional data (columnName, maxes, ...) were to aid me in my search, so you might not need them; I left them here to maybe spark some ideas :-).

Acclaim

Answer 20

case when Col1 < Col2 and Col1 < Col3 then Col1 when Col2 is null and Col3 is null then Col1 when Col1 < Col2 and Col3 is null then Col1 when Col1 < Col3 and Col2 is null then Col1 when Col2 < Col1 and Col2 < Col3 then Col2 when Col1 is null and Col3 is null then Col2 when Col2 < Col1 and Col3 is null then Col2 when Col2 < Col3 and Col1 is null then Col2 when Col3 < Col1 and Col3 < Col2 then Col3 when Col1 is null and Col2 is null then Col3 when Col3 < Col1 and Col2 is null then Col3 when Col3 < Col2 and Col1 is null then Col3 when Col2 = Col3 then Col2 when Col1 = Col3 then Col1 when Col1 = Col2 then Col1 when Col2 = Col3 and Col1 = Col3 then Col1
else null end as 'MIN'

Chon

This was the only way I could successfully do it with 3 columns with null values – Chon

Answer 21

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;

SELECT 
    LEAST(P.SellStartDate, P.OtherDate, P.ThirdDate) AS EarliestDate
FROM SalesLT.Product AS P

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

Dolorisdolorita