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?

Answer 1

I don't think so. I wanted this the other day. The closest I got was:

SELECT
  o.OrderId,
  CASE WHEN o.NegotiatedPrice > o.SuggestedPrice THEN o.NegotiatedPrice 
     ELSE o.SuggestedPrice
  END
FROM Order o

Answer 2

You can do something like this:

select case when o.NegotiatedPrice > o.SuggestedPrice 
then o.NegotiatedPrice
else o.SuggestedPrice
end

Answer 3

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

Answer 4

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

Answer 5

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.HigherArgument(@val1 int, @val2 int)
returns int
as
begin
  if @val1 > @val2
    return @val1
  return @val2
end

....and you would call it like so:

SELECT o.OrderId, dbo.HigherArgument(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o

Answer 6

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) > ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
        THEN ISNULL(o.NegotiatedPrice, o.SuggestedPrice)
        ELSE ISNULL(o.SuggestedPrice, o.NegotiatedPrice)
   END
FROM Order o

Answer 7

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 >= @val2
    return @val1
  if @val1 < @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 < NULL should always return NULL. In other words unknown result.

Answer 8

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 > b then a else b end
from #t

-- option 2 - A union statement 
select a from #t where a >= b 
union all 
select b from #t where b > 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 > @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

Answer 9

sambo99, could you please update your post to link to my post on that question (I supplied the unpivot, not Kristof.) The comments that went along with the unpivot might also be useful to anyone looking for an answer to this question, and they need to know about the PK requirement.

Cheers,

Rick

Answer 10

Can be done in one line:

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.

Answer 11

DECLARE @MAX INT @MAX = (SELECT MAX(VALUE) FROM (SELECT 1 AS VALUE UNION SELECT 2 AS VALUE) AS T1)

Answer 12

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 > @p2 THEN @p1
                         ELSE @p2
                    END
                  )

    RETURN @Result

END

Answer 13

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.

Answer 14

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