What's the best way to select max over multiple fields in SQL?
- by allyourcode
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
