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

Answer 2

Yes, one way is to union together two queries with a "more powerful" sort key (I'm using position rather than column name below):

select 1 as uberkey, date1, column2
  from mytable
  where data1 >= '2012-10-09'
union all
select 2 as uberkey, date1, column2
  from mytable
  where data1 < '2012-10-09'
order by 1 asc, 2 asc

What this will do is use the uberkey as the primary sorting field so that later dates come first. You can simply ignore this column when you display the data.

This can often be more efficient that per-row functions like case since case must do a translation on every row. Two queries with a where clause can simply use an index to throw away the other "half" of the rows and use a constant for the ones retrieved.

And (though I have no particular knowledge about MySQL, I'm a DB2 man myself) intelligent DBMS' can actually parallelise the two subcomponents of the query for efficiency.

As with all unions, make sure you use union all if the two subcomponents are mutually exclusive, so that there's no need for an unnecessary sort-and-remove-duplicates operation.

Answer 3

Normally you cant sort one column in both directions without using UNION. UNION has som disadventages especially if you ar using ORM. If you want yo order by upcoming ASC the past DESC you can do it with trick:

SELECT yourDateColumn,IF(yourDateColumn)

You can also use it with ORM and still use paginators etc.