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

User · Answer

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

User · Answer

Use Case      Select Case When  PaidThisMonth  lt   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  lt   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

User · Answer

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

User · Answer

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

User · Answer

Building on the brilliant logic   code from mathematix and scottyc  I submit   DECLARE  a INT   b INT   c INT   0  WHILE  c  lt  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  lt    b AND  a    a -   ABS  a- b      a- b      2              OR   a  gt    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

User · Answer

SQL Server 2012 and 2014 supports IIF cont true false  function  Thus for minimal selection you can use it like  SELECT IIF first gt second  second  first  the minimal FROM table   While IIF is just a shorthand for writing CASE   WHEN   ELSE  it s easier to write

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  50 THEN  Under  50           WHEN ListPrice  gt   50 and ListPrice  lt  250 THEN  Under  250           WHEN ListPrice  gt   250 and ListPrice  lt  1000 THEN  Under  1000           ELSE  Over  1000        END FROM Production Product ORDER BY ProductNumber   GO

[tsql] Getting the minimum of two values in SQL

Examples related to tsql

Examples related to sql-server