Neat trick to find max value of multiple columns
Finding the maximum value of multiple columns in a single row is not too easy to do. So I thought.
If you've got 2 columns its not bad using a CASE statement is easy, if you've got 3 it becomes tricky, 4 it gets really messy and the code just is ugly.
Long back I figured you could use a UNION when needing to do this on variables but didn't think about doing it with the values of a row.
Louis Davidson posted this neat little trick which uses the ability of a subquery to reference columns from the main rowset to return the maximum value.
select salesOrderId,(select max(dateValue)
from (select orderDate as dateValue
select modifiedDate) as dates) as MaxDateValue,
orderdate, dueDate,shipDate, modifiedDate