Sort by minimum value of two columns

I use SQL Server 2008 R2.

I need to sort a table by the minimal value of two columns.

The table looks like this:

ID: integer; 
Date1: datetime; 
Date2: datetime.

I want my data to be sorted by minimal of two dates.

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

Solution 1:

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

SELECT Id, Date1, Date2
FROM YourTable
ORDER 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, Date2
FROM YourTable
ORDER 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.

Solution 2:

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.)