Getting the minimum of two values in SQL

Question

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.

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

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.

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.