Order by max value in three different columns

Question

I'm not even sure it's possible to do this but I want to order a query based on the maximum value of one of three columns.

Example table structure: guid, column1, column2, column3

Columns 1-3 have numerical values and I want to order the select statement based on the maximum value of 1, 2 OR 3.

For example:

record column1  column2  column3    
---------------------------------
1      5        0        2
2      2        0        6
3      0        1        2

Would be ordered record 2, 1, 3 because 6 is the maximum value of the three fields across the three records, record 1 is the second and record 3 is the third.

Does this make any sense?

Thanks.

Answer 1

It may be possible to do in a select query (possibly using something like case when though I'm not sure that's allowed in the order by clause itself, YMMV depending on the DBMS) but it's rarely a good idea to use per-row calculations if you want your database to scale well as tables get bigger ("not have the performance of a one-legged pig in a horse race", as one of our DBAs eloquently puts it).

In situations like this, I set up an additional (indexed) column to hold the maximum and ensure that the data integrity is maintained by using an insert/update trigger to force that new column to the maximum of the other three.

Because most database tables are read far more often than written, this amortises the cost of the calculation across all the reads. The cost is borne only when the data is updated and the queries become blindingly fast since you're ordering on a single, indexed, column:

select f1, f2, f3 from t order by fmax desc;

Answer 2

As mentioned here, what you want is an equivalent of the GREATEST function.

In the absence of that, and assuming you've defined a UDF LargerOf to return the largest of two numbers, use

SELECT *
FROM Table
ORDER BY LargerOf(LargerOf(column1, column2), column3)

Answer 3

create table myTable
(column1 int, column2 int, column3 int)
go


insert into myTable
values (5, 0 , 2)
go

insert into myTable
values (2, 0 , 6)
go

insert into myTable
values (0, 1 , 2)
go


select *
from mytable
order by case when column1 > column2 and column1 > column3 then column1
when column2 > column3 then column2
else column3 end desc