Sort by minimum value of two columns

NOT NULL columns. You need to add CASE expression into ORDER BY clause in following:

SELECT Id, Date1, Date2FROM YourTableORDER BY CASE            WHEN Date1 < Date2 THEN Date1            ELSE Date2          END 

NULLABLE columns. As Zohar Peled wrote in comments if columns are nullable you could use ISNULL (but better to use COALESCE instead of ISNULL, because It's ANSI SQL standard) in following:

SELECT Id, Date1, Date2FROM YourTableORDER BY CASE            WHEN COALESCE(Date1, '1753-01-01') < COALESCE(Date2, '1753-01-01') THEN Date1            ELSE Date2          END

You can read about ANSI standard dateformat 1753-01-01 here.

Use a CASE expression in the ORDER BY:

 ORDER BY case when date1 < date2 then date1 else date2 end

Edit:

If null values need to be considered, add coalesce():

 ORDER BY case when date1 < date2 then date1 else coalesce(date2,date1) end

Explanation:

If date1 < date2 then order by date1. (Both dates are non null here.) Works just like before.

Else use COALESCE() to order by date2 (when date2 is not null), or date1 (when date2 is null), or by null (if both dates are null.)

The simplest way is using of the VALUES keyword, like the following:

SELECT ID, Date1, Date2FROM YourTableORDER BY (SELECT MIN(v) FROM (VALUES (Date1), (Date2)) AS value(v))

This code will work for all the cases, even with nullable columns.

Edit :

The solution with the COALESCE keyword is not universal. It has the important restrictions:

• It won't work if the columns are of the Date type (if you use the dates before 01/01/1753)
• It won't work in case one of the columns is NULL. It interprets theNULL value as the minimal datetime value. But is it actuallytrue? It isn't even datetime, it is nothing.
• The IF expression will be much more complicated if we use more than two columns.

According to the question:

What is the simplest way to sort this table that way?

The shortest and the simplest solution is the one which described above, because:

• It doesn't take a lot of coding to implement it - simply add an one more line.
• You don't need to care about whether the columns are nullable or not. You just use the code and it works.
• You can extend the number of columns in your query simply by adding the one after a comma.
• It works with the Date columns and you don't need to modify the code.

Edit 2 :

Zohar Peled suggested the following way of order:

I would order the rows by this rules: first, when both null, second, when date1 is null, third, when date 2 is null, fourth, min(date1, date2)

So, for this case the solution can be reached by using of the same approach, like the following:

SELECT ID, Date1, Date2FROM YourTableORDER BY CASE WHEN Date1 IS NULL AND Date2 IS NULL THEN 0     WHEN Date1 IS NULL THEN 1     WHEN Date2 IS NULL THEN 2     ELSE 3 END,(SELECT MIN(v) FROM (VALUES ([Date1]), ([Date2])) AS value(v))

The output for this code is below:

The COALESCE solution will not sort the table this way. It messes up the rows where at least one cell of the NULL value. The output of it is the following:

Hope this helps and waiting for critics.