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

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)