Posted By: skbergam
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?
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.
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