查询首先对即将到来的日期，升序，过去日期，降序进行排序

Question

I have a event table which stored date (m/d/Y) in a following manner

eventid     eventstart      eventend    status
----------------------------------------------
      1     10/9/2012       10/27/2012  Active
      2     4/3/2012        4/27/2012   Active
      3     10/26/2012      10/27/2012  Active
      4     2/7/2012        2/9/2012    Active
      5     10/30/2012      10/31/2012  Active
      6     10/9/2012       10/31/2012  Active
      7     11/9/2012       10/19/2012  Active
      8     10/31/2012      10/18/2012  Active

If I have an input date e.g. 10/29/2012 then I want to sort the startdate in the following manner

eventstart          
----------
10/30/2012
10/31/2012 
11/9/2012
10/26/2012
10/9/2012
10/9/2012

Can anyone help me?

Answer 1

You can use the CASE syntax in the order by clause to sort the two cases separately:

SELECT *
FROM `events`
ORDER BY 
    CASE WHEN eventstart >= '2012-10-29' THEN eventstart ELSE '9999-12-31' END ASC, 
    CASE WHEN eventstart <  '2012-10-29' THEN eventstart ELSE NULL         END DESC

-- eventid | eventstart
-- --------+-----------
-- 5       | 2012-10-30
-- 8       | 2012-10-31
-- 7       | 2012-11-09
-- 3       | 2012-10-26
-- 1       | 2012-10-09
-- 6       | 2012-10-09
-- 2       | 2012-04-03
-- 4       | 2012-02-07