ORDER BY date with past dates after upcoming dates

Question

I need do execute a query on a table in a MySql database where the order of the resulting rows will be like this:

If today is 10/09/12:

...
11/09/12 
12/09/12
15/09/12
08/09/12  <--here start the past dates
07/09/12
05/09/12
....

Is there a way to achive this directly in MySQL?

---

I've resolved in this way:

First, the select statement include a new boolean that mark if the date is past or future by:

SELECT DISTINCT *,CASE  WHEN startdate < CURDATE() THEN 0
                ELSE 1 END AS past_or_future 

Second, I've done a double 'Order by': first on the past_or_future boolean and then on the date, with the conditional like this:

ORDER BY past_or_future  DESC , CASE WHEN past_or_future  = 1 THEN startdate END ASC, CASE WHEN past = 0 THEN startdate END DESC

in this way I've obtained for first all the upcoming dates ordered by date (from the lower value to higher) then all the past dates ordered from the date (from the higher to the lower)

Answer 1

You can still do CASE statement even in ORDER BY clause,

SELECT *
FROM tableName
ORDER BY (CASE WHEN DATE(dateColumn) < DATE(GETDATE())
              THEN 1
              ELSE 0
         END) DESC, dateColumn ASC