Getting the minimum of two values in SQL

Solution 1:

SQL Server 2012 and 2014 supports IIF(cont,true,false) function. Thus for minimal selection you can use it like

SELECT IIF(first>second, second, first) the_minimal FROM table

While IIF is just a shorthand for writing CASE...WHEN...ELSE, it's easier to write.

Solution 2:

The solutions using CASE, IIF, and UDF are adequate, but impractical when extending the problem to the general case using more than 2 comparison values. The generalized solution in SQL Server 2008+ utilizes a strange application of the VALUES clause:

SELECT
PaidForPast=(SELECT MIN(x) FROM (VALUES (PaidThisMonth),(OwedPast)) AS value(x))

Credit due to this website: http://sqlblog.com/blogs/jamie_thomson/archive/2012/01/20/use-values-clause-to-get-the-maximum-value-from-some-columns-sql-server-t-sql.aspx

Solution 3:

Use Case:

   Select Case When @PaidThisMonth < @OwedPast 
               Then @PaidThisMonth Else @OwedPast End PaidForPast

As Inline table valued UDF

CREATE FUNCTION Minimum
(@Param1 Integer, @Param2 Integer)
Returns Table As
Return(Select Case When @Param1 < @Param2 
                   Then @Param1 Else @Param2 End MinValue)

Usage:

Select MinValue as PaidforPast 
From dbo.Minimum(@PaidThisMonth, @OwedPast)

ADDENDUM: This is probably best for when addressing only two possible values, if there are more than two, consider Craig's answer using Values clause.

Solution 4:

I just had a situation where I had to find the max of 4 complex selects within an update. With this approach you can have as many as you like!

You can also replace the numbers with aditional selects

select max(x)
 from (
 select 1 as 'x' union
 select 4 as 'x' union
 select 3 as 'x' union
 select 2 as 'x' 
 ) a

More complex usage

 @answer = select Max(x)
           from (
                select @NumberA as 'x' union
                select @NumberB as 'x' union
                select @NumberC as 'x' union
                select (
                       Select Max(score) from TopScores
                       ) as 'x' 
     ) a

I'm sure a UDF has better performance.

Solution 5:

For MySQL or PostgreSQL 9.3+, a better way is to use the LEAST and GREATEST functions.

SELECT GREATEST(A.date0, B.date0) AS date0, 
       LEAST(A.date1, B.date1, B.date2) AS date1
FROM A, B
WHERE B.x = A.x

With:

GREATEST(value [, ...]) : Returns the largest (maximum-valued) argument from values provided
LEAST(value [, ...]) Returns the smallest (minimum-valued) argument from values provided

Documentation links :

MySQL http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
Postgres https://www.postgresql.org/docs/current/functions-conditional.html