Getting the minimum of two values in SQL

Question

I have two variables, one is called PaidThisMonth, and the other is called OwedPast. They are both results of some subqueries in SQL. How can I select the smaller of the two and return it as a value titled PaidForPast?

The MIN function works on columns, not variables.

If you're on Postgres or MySQL, skip down to @Gil_Margolin's answer. — Noumenon, Apr 22 '18 at 04:38

score 210 · Answer 1 · edited Aug 23 '19 at 08:53

210

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.

edited Aug 23 '19 at 08:53

NoDataDumpNoContribution

10,591
9
64
104

answered Aug 21 '14 at 16:27

Mert Gülsoy

2,779
1
19
23

9

[`IIF`](https://msdn.microsoft.com/en-us/library/hh213574.aspx?f=255&MSPPError=-2147217396) is just a syntactic sugar for `CASE...WHEN...ELSE`. – Salman A Apr 15 '15 at 07:01
67

Possibly yes. But easier to write. – Mert Gülsoy Apr 15 '15 at 07:17
9

@MertGülsoy And easier to read, which should be at the top of everyone's priority list, right after correctness. – Daniel Apr 14 '20 at 15:31
This should be the accepted answer. As a front-end developer, `CASE...WHEN...ELSE` is so awkward to read. `IIF` is way clearer and shorter. – NearHuscarl Nov 04 '20 at 13:13
3

IIF(onlyRequireSQLServerGTE2012Support, thisAnswer, theOtherAnswer) – Andrew Feb 19 '21 at 03:25

score 169 · Answer 2 · answered Aug 25 '15 at 16:40

169

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

answered Aug 25 '15 at 16:40

Craig

4,492
2
19
22

1

if you want the min non-zero: `MIN(x*(case x when 0 then null else 1 end))` – mpag Nov 09 '17 at 03:39
1

Except MartinC gave the same answer four years earlier, and actually showed it with more than two values... – Auspex Dec 21 '17 at 13:28
10

Auspex, MartinC's answer is unrelated. This answer does not use unions. – Craig Dec 23 '17 at 02:38
Broken link in the credit due. – akousmata Sep 17 '21 at 17:47
Would be a nice solution, however, limited: Cannot perform an aggregate function on an expression containing an aggregate or a subquery The only thing I care about here is to do min(a,b) and it must be short, simply for cleanlyness of the code – Daniel Apr 28 '22 at 12:49
The subquery selecting from a `VALUES` constructor or a derived table using `UNION ALL` are basically the same thing. Both are unpivoting columns to rows and running an aggregate on them and will have the same plan. – Martin Smith Jan 31 '23 at 14:02
i.e. to use it with the same example as this answer `SELECT PaidForPast=(SELECT MIN(x) FROM (SELECT PaidThisMonth UNION ALL SELECT OwedPast) AS value(x))` – Martin Smith Jan 31 '23 at 14:07

score 147 · Accepted Answer · edited Nov 01 '18 at 03:52

147

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.

edited Nov 01 '18 at 03:52

Pang

9,564
146
81
122

answered Dec 22 '09 at 17:10

Charles Bretana

143,358
22
150
216

better understandable syntax: return(select minValue = case when @@param1 < @@param2 then @@param1 else @@param2 end). Ok this may not be normalized, i don't know. But it's much more understandable and should be normalized. – Softlion Jan 16 '12 at 11:49
1

Another reason to prefer @Craig's answer below is due to null handling. If the values being compared are nullable, and _one_ of the values being compared is null, the switch-case shown might return null or the value, depending on the order of the WHEN test (unless you add use of ISNULL). Craig's approach will always prefer selection of the not-null value which seems more correct to me, at least in my current use-case in the comparison of nullable dates. – Nij Feb 12 '19 at 08:25

score 50 · Answer 4 · edited Jan 31 '23 at 14:03

50

For SQL Server 2022+ (or 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 :

edited Jan 31 '23 at 14:03

Martin Smith

438,706
87
741
845

answered Jun 09 '16 at 21:57

Gil Margolin

1,959
20
21

This also works in PostgreSQL (and it's exactly what I was looking for :) See: https://www.postgresql.org/docs/9.5/static/functions-conditional.html – Albert Vaca Cintora Aug 23 '17 at 14:53
1

This is the best answer by far. – Roberto Rodriguez Mar 15 '19 at 02:21
6

@RobertoRodriguez it would be the best if the question had MySQL or PostgreSQL tagged as part of the question. The question was specifically about tsql so this answer does not help at all. – Jmaurier Oct 09 '19 at 15:27
2

this is not answer for MSSQL – Mujah Maskey Mar 09 '20 at 19:08
2

`GREATEST` and `LEAST` are promised to be [available in Sql2022](https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-least-transact-sql?view=azure-sqldw-latest), so this answer was just over 6 years too early. ;) – Granger Jul 08 '22 at 16:09
Works in Azure SQL (https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=azuresqldb-current). Thanks! – Honza Nov 14 '22 at 13:44

score 37 · Answer 5 · answered Mar 08 '11 at 13:29

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.

I like that one the most since it's basic SQL. Furthermore, UDFs are not necessarilly faster. For most column stores, each attribute (I assume you're also going to filter on the attributes) can be computed in parallel and just the qualifying set is unioned. So unions are not slow per se. — Bouncner, May 02 '16 at 06:25

score 16 · Answer 6 · answered Nov 29 '15 at 12:14

16

Here is a trick if you want to calculate maximum(field, 0):

SELECT (ABS(field) + field)/2 FROM Table

returns 0 if field is negative, else, return field.

answered Nov 29 '15 at 12:14

mathematix

366
3
10

5

So, to calculate minimum(@a, @b), you could use: `SELECT @a - ( ABS(@a-@b) + (@a-@b) ) / 2` – scottyc Jan 28 '16 at 19:09
1

and don't forget about type overflow ;) – pkuderov May 12 '17 at 13:22
Is this save from floating-point precision standpoint? Is it certain, that the result will never be something close to zero but negative? – zuraff Oct 17 '17 at 12:22

score 6 · Answer 7 · edited Nov 01 '18 at 03:46

Use a CASE statement.

Example B in this page should be close to what you're trying to do:
http://msdn.microsoft.com/en-us/library/ms181765.aspx

Here's the code from the page:

USE AdventureWorks;
GO
SELECT   ProductNumber, Name, 'Price Range' = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO

score 3 · Answer 8 · edited Dec 12 '22 at 14:52

This works for up to 5 dates and handles nulls. Just couldn't get it to work as an Inline function.

CREATE FUNCTION dbo.MinDate(@Date1 datetime = Null,
                            @Date2 datetime = Null,
                            @Date3 datetime = Null,
                            @Date4 datetime = Null,
                            @Date5 datetime = Null)
RETURNS Datetime AS
BEGIN
--USAGE select dbo.MinDate('20120405',null,null,'20110305',null)
DECLARE @Output datetime;

WITH Datelist_CTE(DT)
AS (
        SELECT @Date1 AS DT WHERE @Date1 is not NULL UNION
        SELECT @Date2 AS DT WHERE @Date2 is not NULL UNION
        SELECT @Date3 AS DT WHERE @Date3 is not NULL UNION
        SELECT @Date4 AS DT WHERE @Date4 is not NULL UNION
        SELECT @Date5 AS DT WHERE @Date5 is not NULL
   )
Select @Output=Min(DT) FROM Datelist_CTE;

RETURN @Output;
END;

Just realised you don't need the WHERE Clauses as MIN will remove Nulls anyway. — Lawrence, Jul 04 '13 at 11:49

score 2 · Answer 9 · edited Dec 12 '22 at 14:49

Building on the brilliant logic / code from mathematix and scottyc, I submit:

DECLARE @a INT, @b INT, @c INT = 0;

WHILE @c < 100
    BEGIN
        SET @c += 1;
        SET @a = ROUND(RAND()*100,0)-50;
        SET @b = ROUND(RAND()*100,0)-50;
        SELECT @a AS a, @b AS b,
            @a - ( ABS(@a-@b) + (@a-@b) ) / 2 AS MINab,
            @a + ( ABS(@b-@a) + (@b-@a) ) / 2 AS MAXab,
            CASE WHEN (@a <= @b AND @a = @a - ( ABS(@a-@b) + (@a-@b) ) / 2)
            OR (@a >= @b AND @a = @a + ( ABS(@b-@a) + (@b-@a) ) / 2)
            THEN 'Success' ELSE 'Failure' END AS Status;
    END;

Although the jump from scottyc's MIN function to the MAX function should have been obvious to me, it wasn't, so I've solved for it and included it here: SELECT @a + ( ABS(@b-@a) + (@b-@a) ) / 2. The randomly generated numbers, while not proof, should at least convince skeptics that both formulae are correct.

score 1 · Answer 10 · answered Jan 11 '13 at 16:34

1

Use a temp table to insert the range of values, then select the min/max of the temp table from within a stored procedure or UDF. This is a basic construct, so feel free to revise as needed.

For example:

CREATE PROCEDURE GetMinSpeed() AS
BEGIN

    CREATE TABLE #speed (Driver NVARCHAR(10), SPEED INT);
    '
    ' Insert any number of data you need to sort and pull from
    '
    INSERT INTO #speed (N'Petty', 165)
    INSERT INTO #speed (N'Earnhardt', 172)
    INSERT INTO #speed (N'Patrick', 174)

    SELECT MIN(SPEED) FROM #speed

    DROP TABLE #speed

END

answered Jan 11 '13 at 16:34

user1970604

19
1

What use is a procedure that gets the minimum of a set of *hardcoded* values? Furthermore, you have multiple syntax errors: * It seems `()` is not a valid parameter list. To declare a procedure with no parameters, it seems you need to not have brackets at all. * `'` is not a comment marker in SQL. * You're missing the `VALUES` keyword. * You're missing the semicolons. It's important not to leave these off: https://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server#answer-26356175 – Stewart Dec 12 '22 at 14:47

score 0 · Answer 11 · answered Jan 18 '22 at 12:46

0

Select MIN(T.V) FROM (Select 1 as V UNION Select 2 as V) T

answered Jan 18 '22 at 12:46

Sergej Loos

292
1
2
10

score -2 · Answer 12 · answered Jan 31 '23 at 13:53

-2

SELECT (WHEN first > second THEN second ELSE first END) the_minimal FROM table

answered Jan 31 '23 at 13:53

Сергей Седых

120
1
4

2

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Jan 31 '23 at 20:08
Also, that's especially important here, where there's existing answers that have been validated by the community, including one with nearly 200 upvotes. What sets your answer apart? Under what conditions would your answer be preferred? – Jeremy Caney Jan 31 '23 at 20:09
Not valid syntax. Missing `CASE` keyword. And if made valid syntax then repeats existing answers – Martin Smith Feb 01 '23 at 08:13

Getting the minimum of two values in SQL

12 Answers12

Linked

Related