Is there a Max function in SQL Server that takes two values like Math Max in NET

Question

I want to write a query like this   SELECT o OrderId  MAX o NegotiatedPrice  o SuggestedPrice  FROM Order o   But this isn t how the MAX function works  right   It is an aggregate function so it expects a single parameter and then returns the MAX of all rows      Does anyone know how to do it my way

User · Answer

CREATE FUNCTION  dbo   fnMax    p1 INT   p2 INT  RETURNS INT AS BEGIN      DECLARE  Result INT      SET  p2   COALESCE  p2   p1       SELECT          Result                        SELECT                     CASE WHEN  p1  gt   p2 THEN  p1                          ELSE  p2                     END                          RETURN  Result  END

User · Answer

Why not try IIF function  requires SQL Server 2012 and later   IIF a gt b  a  b    That s it    Hint  be careful about either would be null  since the result of a gt b will be false whenever either is null  So b will be the result in this case

User · Answer

SELECT o OrderId     --MAX o NegotiatedPrice  o SuggestedPrice     SELECT MAX v  FROM  VALUES  o NegotiatedPrice    o SuggestedPrice   AS value v   as ChoosenPrice   FROM Order o

User · Answer

SQL Server 2012 introduced IIF   SELECT      o OrderId       IIF  ISNULL  o NegotiatedPrice  0    gt  ISNULL  o SuggestedPrice  0             o NegotiatedPrice            o SuggestedPrice        FROM      Order o   Handling NULLs is recommended when using IIF  because a NULL on either side of your boolean expression will cause IIF to return the false value  as opposed to NULL

User · Answer

Here is an IIF version with NULL handling  based on of Xin s answer    IIF a IS NULL OR b IS NULL  ISNULL a b   IIF a  gt  b  a  b     The logic is as follows  if either of the values is NULL  return the one that isn t NULL  if both are NULL  a NULL is returned   Otherwise return the greater one   Same can be done for MIN   IIF a IS NULL OR b IS NULL  ISNULL a b   IIF a  lt  b  a  b

User · Answer

Expanding on Xin s answer and assuming the comparison value type is INT  this approach works too   SELECT IIF ISNULL  A  -2147483648   gt  ISNULL  B  -2147483648    A   B    This is a full test with example values   DECLARE  A AS INT DECLARE  B AS INT  SELECT   A   2   B   1 SELECT  IIF ISNULL  A  -2147483648   gt  ISNULL  B  -2147483648    A   B  -- 2  SELECT   A   2   B   3 SELECT  IIF ISNULL  A  -2147483648   gt  ISNULL  B  -2147483648    A   B  -- 3  SELECT   A   2   B   NULL SELECT  IIF ISNULL  A  -2147483648   gt  ISNULL  B  -2147483648    A   B  -- 2      SELECT   A   NULL   B   1 SELECT  IIF ISNULL  A  -2147483648   gt  ISNULL  B  -2147483648    A   B  -- 1

User · Answer

For the answer above regarding large numbers  you could do the multiplication before the addition subtraction  It s a bit bulkier but requires no cast   I can t speak for speed but I assume it s still pretty quick        SELECT 0 5      val1    val2      ABS  val1 -  val2     Changes to     SELECT  val1 0 5  val2 0 5     ABS  val1 0 5 -  val2 0 5    at least an alternative if you want to avoid casting

User · Answer

Can be done in one line   -- the following expression calculates    gt  max  val1   val2  SELECT 0 5      val1    val2    ABS  val1 -  val2      Edit  If you re dealing with very large numbers you ll have to convert the value variables into bigint in order to avoid an integer overflow

User · Answer

You can do something like this   select case when o NegotiatedPrice  gt  o SuggestedPrice  then o NegotiatedPrice else o SuggestedPrice end

User · Answer

I probably wouldn t do it this way  as it s less efficient than the already mentioned CASE constructs - unless  perhaps  you had covering indexes for both queries  Either way  it s a useful technique for similar problems   SELECT OrderId  MAX Price  as Price FROM      SELECT o OrderId  o NegotiatedPrice as Price FROM Order o    UNION ALL    SELECT o OrderId  o SuggestedPrice as Price FROM Order o   as A GROUP BY OrderId

User · Answer

Oops  I just posted a dupe of this question      The answer is  there is no built in function like Oracle s Greatest  but you can achieve a similar result for 2 columns with a UDF  note  the use of sql variant is quite important here   create table  t  a int  b int    insert  t select 1 2 union all  select 3 4 union all select 5 2  -- option 1 - A case statement select case when a  gt  b then a else b end from  t  -- option 2 - A union statement  select a from  t where a  gt   b  union all  select b from  t where b  gt  a   -- option 3 - A udf create function dbo GREATEST         a as sql variant       b as sql variant   returns sql variant begin        declare  max sql variant      if  a is null or  b is null return null     if  b  gt   a return  b       return  a  end   select dbo GREATEST a b  from  t   kristof  Posted this answer    create table  t  id int IDENTITY 1 1   a int  b int  insert  t select 1 2 union all select 3 4 union all select 5 2  select id  max val  from  t     unpivot  val for col in  a  b   as unpvt group by id

User · Answer

Can be done in one line   -- the following expression calculates    gt  max  val1   val2  SELECT 0 5      val1    val2    ABS  val1 -  val2      Edit  If you re dealing with very large numbers you ll have to convert the value variables into bigint in order to avoid an integer overflow

User · Answer

I would go with the solution provided by kcrumley Just modify it slightly to handle NULLs  create function dbo HigherArgumentOrNull  val1 int   val2 int  returns int as begin   if  val1  gt    val2     return  val1   if  val1  lt   val2     return  val2   return NULL end   EDIT Modified after comment from Mark  As he correctly pointed out in 3 valued logic x   NULL or x  lt  NULL should always return NULL  In other words unknown result

User · Answer

SELECT o OrderID CASE WHEN o NegotiatedPrice  gt  o SuggestedPrice THEN  o NegotiatedPrice ELSE  o SuggestedPrice END AS Price

User · Answer

You d need to make a User-Defined Function if you wanted to have syntax similar to your example  but could you do what you want to do  inline  fairly easily with a CASE statement  as the others have said   The UDF could be something like this   create function dbo InlineMax  val1 int   val2 int  returns int as begin   if  val1  gt   val2     return  val1   return isnull  val2  val1  end       and you would call it like so      SELECT o OrderId  dbo InlineMax o NegotiatedPrice  o SuggestedPrice   FROM Order o

User · Answer

Here s a case example that should handle nulls and will work with older versions of MSSQL   This is based on the inline function in one one of the popular examples   case   when a  gt   b then a   else isnull b a  end

User · Answer

Here is  Scott Langham s answer with simple NULL handling   SELECT       o OrderId        CASE WHEN  o NegotiatedPrice  gt  o SuggestedPrice OR o SuggestedPrice IS NULL            THEN o NegotiatedPrice           ELSE o SuggestedPrice       END As MaxPrice FROM Order o

User · Answer

The other answers are good  but if you have to worry about having NULL values  you may want this variant   SELECT o OrderId      CASE WHEN ISNULL o NegotiatedPrice  o SuggestedPrice   gt  ISNULL o SuggestedPrice  o NegotiatedPrice          THEN ISNULL o NegotiatedPrice  o SuggestedPrice          ELSE ISNULL o SuggestedPrice  o NegotiatedPrice     END FROM Order o

User · Answer

If you re using SQL Server 2008  or above   then this is the better solution   SELECT o OrderId          SELECT MAX Price          FROM  VALUES  o NegotiatedPrice   o SuggestedPrice   AS AllPrices Price   FROM Order o   All credit and votes should go to Sven s answer to a related question   SQL MAX of multiple columns   I say it s the  best answer  because    It doesn t require complicating your code with UNION s  PIVOT s  UNPIVOT s  UDF s  and crazy-long CASE statments  It isn t plagued with the problem of handling nulls  it handles them just fine  It s easy to swap out the  MAX  with  MIN    AVG   or  SUM    You can use any aggregate function to find the aggregate over many different columns  You re not limited to the names I used  i e   AllPrices  and  Price     You can pick your own names to make it easier to read and understand for the next guy  You can find multiple aggregates using SQL Server 2008 s derived tables like so  SELECT MAX a   MAX b  FROM  VALUES  1  2    3  4    5  6    7  8    9  10    AS MyTable a  b

User · Answer

DECLARE  MAX INT  MAX    SELECT MAX VALUE                  FROM  SELECT 1 AS VALUE UNION                       SELECT 2 AS VALUE  AS T1

User · Answer

-- Simple way without  functions  or  IF  or  CASE   -- Query to select maximum value  SELECT o OrderId     SELECT MAX v     FROM  VALUES  o NegotiatedPrice    o SuggestedPrice   AS value v   AS MaxValue   FROM Order o

User · Answer

I don t think so  I wanted this the other day  The closest I got was   SELECT   o OrderId    CASE WHEN o NegotiatedPrice  gt  o SuggestedPrice THEN o NegotiatedPrice       ELSE o SuggestedPrice   END FROM Order o

User · Answer

Try this   It can handle more than 2 values SELECT Max v  FROM  VALUES  1    2    3   AS value v

User · Answer

Sub Queries can access the columns from the Outer query so you can use this approach to use aggregates such as MAX across columns   Probably more useful when there is a greater number of columns involved though    WITH  Order  AS   SELECT 1 AS OrderId  100 AS NegotiatedPrice  110 AS SuggestedPrice UNION ALL SELECT 2 AS OrderId  1000 AS NegotiatedPrice  50 AS SuggestedPrice   SELECT        o OrderId           SELECT MAX price FROM              SELECT o NegotiatedPrice AS price              UNION ALL SELECT o SuggestedPrice  d           AS MaxPrice  FROM   Order   o

User · Answer

In Presto you could use use  SELECT array max ARRAY o NegotiatedPrice  o SuggestedPrice

User · Answer

In SQL Server 2012 or higher   you can use a combination of IIF and ISNULL  or COALESCE  to get the maximum of 2 values  Even when 1 of them is NULL  IIF col1  gt   col2  col1  ISNULL col2  col1     Or if you want it to return 0 when both are NULL IIF col1  gt   col2  col1  COALESCE col2  col1  0     Example snippet  -- use table variable for testing purposes declare  Order table      OrderId int primary key identity 1 1     NegotiatedPrice decimal 10 2     SuggestedPrice decimal 10 2      -- Sample data insert into  Order  NegotiatedPrice  SuggestedPrice  values  0  1    2  1    3  null    null  4    -- Query SELECT       o OrderId  o NegotiatedPrice  o SuggestedPrice        IIF o NegotiatedPrice  gt   o SuggestedPrice  o NegotiatedPrice  ISNULL o SuggestedPrice  o NegotiatedPrice   AS MaxPrice FROM  Order o  Result  OrderId NegotiatedPrice SuggestedPrice  MaxPrice 1       0 00            1 00            1 00 2       2 00            1 00            2 00 3       3 00            NULL            3 00 4       NULL            4 00            4 00  But if one needs the maximum of multiple columns  Then I suggest a CROSS APPLY on an aggregation of the VALUES  Example  SELECT t     ca  Maximum    ca  Minimum   ca  Total   ca  Average  FROM SomeTable t CROSS APPLY      SELECT      MAX v col  AS  Maximum        MIN v col  AS  Minimum        SUM v col  AS  Total        AVG v col  AS  Average     FROM  VALUES  t Col1    t Col2    t Col3    t Col4   v col    ca  This has the extra benefit that this can calculate other things at the same time

User · Answer

select OrderId        select max  Price   from           select NegotiatedPrice  Price          union all         select SuggestedPrice       p   from  Order

User · Answer

Its as simple as this    CREATE FUNCTION InlineMax        p1 sql variant       p2 sql variant    RETURNS sql variant AS BEGIN     RETURN CASE          WHEN  p1 IS NULL AND  p2 IS NOT NULL THEN  p2          WHEN  p2 IS NULL AND  p1 IS NOT NULL THEN  p1         WHEN  p1  gt   p2 THEN  p1         ELSE  p2 END END

User · Answer

In its simplest form     CREATE FUNCTION fnGreatestInt   Int1 int   Int2 int   RETURNS int AS BEGIN      IF  Int1  gt   ISNULL  Int2  Int1          RETURN  Int1     ELSE         RETURN  Int2      RETURN NULL --Never Hit  END

User · Answer

Oops  I just posted a dupe of this question      The answer is  there is no built in function like Oracle s Greatest  but you can achieve a similar result for 2 columns with a UDF  note  the use of sql variant is quite important here   create table  t  a int  b int    insert  t select 1 2 union all  select 3 4 union all select 5 2  -- option 1 - A case statement select case when a  gt  b then a else b end from  t  -- option 2 - A union statement  select a from  t where a  gt   b  union all  select b from  t where b  gt  a   -- option 3 - A udf create function dbo GREATEST         a as sql variant       b as sql variant   returns sql variant begin        declare  max sql variant      if  a is null or  b is null return null     if  b  gt   a return  b       return  a  end   select dbo GREATEST a b  from  t   kristof  Posted this answer    create table  t  id int IDENTITY 1 1   a int  b int  insert  t select 1 2 union all select 3 4 union all select 5 2  select id  max val  from  t     unpivot  val for col in  a  b   as unpvt group by id

User · Answer

For SQL Server 2012   SELECT      o OrderId       IIF  o NegotiatedPrice  gt   o SuggestedPrice           o NegotiatedPrice            ISNULL o SuggestedPrice  o NegiatedPrice         FROM      Order o

User · Answer

In MemSQL do the following   -- DROP FUNCTION IF EXISTS InlineMax  DELIMITER    CREATE FUNCTION InlineMax val1 INT  val2 INT  RETURNS INT AS DECLARE   val3 INT   0  BEGIN  IF val1  gt  val2 THEN    RETURN val1   ELSE    RETURN val2   END IF   END    DELIMITER    SELECT InlineMax 1 2  as test

[sql] Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

Examples related to sql

Examples related to sql-server

Examples related to max