CODEWITHSUNDEEP
phpmysqlsqldatesql-order-by
Renjith R
Renjith R

Reputation: 891

Query to sort upcoming dates first, ascending, then past dates, descending

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?

Upvotes: 0

Views: 956

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272146

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

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

select * from event where EXTRACT(MONTH FROM str_to_date(eventstart, '%d/%m/%Y'))>=EXTRACT(MONTH FROM str_to_date('10/29/2012', '%d/%m/%Y'))
and eventstart >'10/29/2012'
union
select * from event where EXTRACT(MONTH FROM str_to_date(eventstart, '%d/%m/%Y'))>=EXTRACT(MONTH FROM str_to_date('10/29/2012', '%d/%m/%Y'))
and eventstart <'10/29/2012'

Upvotes: 0

Sergi Juanola
Sergi Juanola

Reputation: 6647

In a really schematic way:

select * from tablename where eventstart >= '10/29/2012' order by eventstart asc
union
select * from tablename where eventstart < '10/29/2012' order by eventstart desc

The first line would get the unstarted events, ordered ascending, and the third one would get the rest.

You should then manage to truly compare those dates with a SQL function or so, and to decide if you want to include today as an unstarted event or as a past event. I posted the first case. If you want today to be a past event, remove the equal symbol from the first comparison and move it to the second one:

select * from tablename where eventstart > '10/29/2012' order by eventstart asc
union
select * from tablename where eventstart <= '10/29/2012' order by eventstart desc

Upvotes: 1

Related Questions