Sort by minimum value of two columns

Question

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?

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

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