What's the best way to select max over multiple fields in SQL?

Question

The I kind of want to do is select max(f1, f2, f3). I know this doesn't work, but I think what I want should be pretty clear (see update 1).

I was thinking of doing select max(concat(f1, '--', f2 ...)), but this has various disadvantages. In particular, doing concat will probably slow things down. What's the best way to get what I want?

update 1: The answers I've gotten so far aren't what I'm after. max works over a set of records, but it compares them using only one value; I want max to consider several values, just like the way order by can consider several values.

update 2: Suppose I have the following table:

id class_name order_by1 order_by_2
 1          a         0          0
 2          a         0          1
 3          b         1          0
 4          b         0          9

I want a query that will group the records by class_name. Then, within each "class", select the record that would come first if you ordered by order_by1 ascending then order_by2 ascending. The result set would consist of records 2 and 3. In my magical query language, it would look something like this:

select max(* order by order_by1 ASC, order_by2 ASC)
from table
group by class_name

Answer 1

Select max(val)
From
(
  Select max(fld1) as val FROM YourTable
  union
  Select max(fld2) as val FROM YourTable
  union
  Select max(fld3) as val FROM YourTable
) x

Edit: Another alternative is:

SELECT
    CASE 
     WHEN MAX(fld1) >= MAX(fld2) AND MAX(fld1) >= MAX(fld3) THEN MAX(fld1)
     WHEN MAX(fld2) >= MAX(fld1) AND MAX(fld2) >= MAX(fld3) THEN MAX(fld2)
     WHEN MAX(fld3) >= MAX(fld1) AND MAX(fld3) >= MAX(fld2) THEN MAX(fld3)
    END AS MaxValue
FROM YourTable

Answer 2

You don't mention the database system you are working with. In Oracle you can do:

select max(greatest(f1, f2, f3))
from T1

or equivalent:

select greatest(max(f1), max(f2), max(f3))
from T1

Answer 3

I have one of these.

CREATE FUNCTION [dbo].[MathMax]  
(  
 @a int,  
 @b int  
)  
RETURNS int  
WITH SCHEMABINDING  
AS  
BEGIN  
 IF @a IS NULL AND @b IS NULL   
  RETURN 0  

 IF @a IS NULL   
  RETURN @b  

 IF @b IS NULL   
  RETURN @a  

 IF @a < @b   
  RETURN @b  

 RETURN @a  
END

Then I can do SELECT dbo.MathMax(dbo.MathMax(MAX(f1), MAX(f2)), MAX(f3)) FROM T1

I believe this performs about the same as the CASE while being more readable, and performs much better than the multi-UNION (even the slightly more efficient UNION ALL).

Answer 4

Based on an answer I gave to another question: http://stackoverflow.com/questions/1015689/sql-select-max-and-accompanying-field/2345892#2345892

To make it work for multiple columns, add more columns to the inner select's ORDER BY.