Search code examples
phpmysqlsqlmysql-workbenchnavicat

MySQL order by date until a specific date, after it reverse order

Ok, so I have a table something like this

+----+-------+------------+
| id | title |    date    |
+----+-------+------------+
|  1 | aaa   | 2018-08-13 |
|  2 | bbb   | 2018-08-02 |
|  3 | ccc   | 2018-07-06 |
|  4 | ddd   | 2018-07-16 |
|  5 | fff   | 2018-07-13 |
+----+-------+------------+

and I want to sort it by date column ascending, but only until a specific date, after that I want to sort it descending, like this

+----+-------+------------+
| id | title |    date    |
+----+-------+------------+
|  4 | ddd   | 2018-07-16 |
|  2 | bbb   | 2018-08-02 |
|  1 | aaa   | 2018-08-13 |
|  5 | fff   | 2018-07-13 |
|  3 | ccc   | 2018-07-06 |
+----+-------+------------+

I thought I could use UNION, but either I don't know how to use it, or it doesn't accept ORDER BY clauses specified in each SELECT.

EDIT: I probably didn't explain myself so good. In my query I would need this to sort ascending by date all of the rows after a specific date (>= some date, in this case let's use 2018-07-15) and all of the rows before to be sorted descending.

Solution

  • You should do this with a single order by and no subquery:

    order by (case when date >= @date then 1 else 0 end),  -- put more recent first
         (case when date >= @date then date end) asc,  -- order the first group ascending
         date desc  -- order by second group descending