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