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
                             union all
                             select dueDate
                             union all
                             select shipDate
                             union all
                             select modifiedDate) as dates) as MaxDateValue,
                    orderdate, dueDate,shipDate, modifiedDate
from sales.SalesOrderHeader

Published 08 May 2006 07:23 by simonsabin
Filed under:


No Comments