Sorting Events in MySQL by start and / or end date

user_10375590 0
Viewed 603

I have the following starting position:

In a table are events with a start and end date.

I would like to sort these events in the query with the following dimensions:

current events > future events > completed events

SELECT 
    startAt,
    endAt,
    (
        CASE
            WHEN DATE(endAt) > DATE(NOW()) AND DATE(startAt) < DATE(NOW()) THEN 1
            WHEN DATE(endAt) > DATE(NOW()) AND DATE(startAt) > DATE(NOW()) THEN 0
            ELSE -1
        END
    ) as position
FROM 
    events
ORDER BY
    position DESC

Now the problem:

Sorting by the three categories works with the above query, but within the categories the following sortings must be applied:

Current events (sorted by end date) > future events (sorted by start date) > completed events (sorted by end date)

So first all running events that will expire soon will come in descending order (i.e. have more time than the previous ones), then all future events that will start soon in descending order (i.e. start later than the previous ones) and then all completed events in descending order.

SOLVED with the help of @Tim Biegeleisen

SELECT 
    startAt,
    endAt
FROM 
    events
ORDER BY
    (
        CASE
            WHEN endAt > CURDATE() AND startAt < CURDATE() THEN 1
            WHEN endAt > CURDATE() AND startAt > CURDATE() THEN 0
            ELSE -1
        END
    ) DESC,
    (
        CASE 
            WHEN startAt < CURDATE() THEN endAt 
            ELSE startAt 
        END
    ) ASC
sql-order-by date sorting mysql sql
2 Answers

You may try this logic:

SELECT
    startAt,
    endAt
FROM events
ORDER BY
    CASE WHEN startAt < CURDATE() THEN endAt ELSE startAt END;

The logic of the above ORDER BY clause is that if an event's start date be strictly less than right now, then it means that the event is either in progress or completely finished, in which case we sort using its endAt value. Otherwise, we sort using startAt.

Based on your comments below, it seems that you also want to first order records by the event status, then order within each group:

SELECT startAt, endAt
FROM events
ORDER BY
    CASE WHEN endAt > CURDATE() AND startAt < CURDATE() THEN 0
         WHEN endAt > CURDATE() AND startAt > CURDATE() THEN 1
         ELSE 2 END,
    CASE WHEN startAt < CURDATE() THEN endAt ELSE startAt END;
user_18632290 
Try case statement to select he date for order.

  SELECT 
    startAt,
    endAt,
    (
        CASE
            WHEN DATE(endAt) > DATE(NOW()) AND DATE(startAt) < DATE(NOW()) THEN 1
            WHEN DATE(endAt) > DATE(NOW()) AND DATE(startAt) > DATE(NOW()) THEN 0
            ELSE -1
        END
    ) as position
  FROM 
        events
  ORDER BY
      CASE
          WHEN DATE(endAt) > DATE(NOW()) AND DATE(startAt) < DATE(NOW()) THEN endAt
          WHEN DATE(endAt) > DATE(NOW()) AND DATE(startAt) > DATE(NOW()) THEN startAt
      ELSE endAt
      END DESC
user_124034720
Related