Query to search for maximum value in a row, rather than within a column - SQL/MySQL

Ok so I have a row in a table. This row has eight date type fields.

I am attempting to write a query that returns the highest date value from a row.

For example:

    NULL,NULL,2009-10-09,2010-03-12,2010-04-15,2010-06-23,2010-08-27,NULL

The query when run against the above table would return the following value for this row: 2010-08-27

I've tried a couple of combinations using CASE, but it seems i开发者_高级运维nelegant and lengthy (and as such I haven't finished writing the query).

Are there any other options available to me? Or does someone have a solution that they have run successfully?

Appreciate any help that can be offered. :)

You want the GREATEST function:

SELECT GREATEST(col1, col2, col3, col4, col5, col6, col7, col8) AS max_date
  FROM YOUR_TABLE

Mind the data types - Don't want to be comparing strings that should be DATEs/etc.

Pity that SQL Server doesn't have it, but thankfully Oracle and PostgreSQL do.

Pity that SQL Server doesn't have it, but thankfully Oracle and PostgreSQL do.

But SQL Server allows other things like selecting without a table (no "FROM DUAL" business; MySQL supports this too) and also subqueries that reference the table 2 levels out like the query below

select a,b,c,d,
    (
    select max(a)
    from
        (select a union all
         select b union all
         select c union all
         select d) columnstorows
    ) greatest
from (select 1 as a,2 as b,null as c,4 as d) virtualtable

No ISNULL business, just a straight max.

As OMG Ponies has mentioned, MySQL GREATEST requires IFNULL around each column. But even that is not the whole answer. If you use IFNULL and provide a "virtual minimum" for each, you could get the wrong answer when all the values are null. You should IFNULL them to another column, either rotate them or just any other column will do really.

select
a,b,c,d,
greatest(ifnull(a,b), ifnull(b,c), ifnull(c,d), ifnull(d,a))
from (select 1 as a,2 as b,null as c,4 as d) virtualtable