Search code examples
sqlsql-servermax

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

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?

Solution

  • 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