Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 212
skb Avatar asked Sep 23 '08 22:09

skb


1 Answers

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)
like image 196
MikeTeeVee Avatar answered Sep 22 '22 14:09

MikeTeeVee