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

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:

  1. It doesn't require complicating your code with UNION's, PIVOT's, UNPIVOT's, UDF's, and crazy-long CASE statments.
  2. It isn't plagued with the problem of handling nulls, it handles them just fine.
  3. 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.
  4. 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.
  5. 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)

Can be done in one line:

-- the following expression calculates ==> 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.


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 > @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