CODEWITHSUNDEEP
phpmysqlsqlsql-order-by
doitlikejustin
doitlikejustin

Reputation: 6353

MySQL order by today, past, future

I am trying to use ORDER BY with a date to get the records from my table in the following order:

TODAY (9 PM)
TODAY (7 PM)
TODAY (9 AM)
TODAY (7 AM)
PAST (3 DAYS AGO)
PAST (2 DAYS AGO)
PAST (1 DAYS AGO)
FUTURE (IN 1 DAY)
FUTURE (IN 2 DAYS)
FUTURE (IN 3 DAYS)

Currently I am using:

SELECT * 
FROM my_table 
ORDER BY 
    IF(DATE(my_date) = DATE(NOW()), 0, 1) ASC,
    IF(DATE(my_date) < DATE(NOW()), 0, 1) ASC,
    my_date ASC

But that gives me:

TODAY (7 AM) - WRONG
TODAY (9 AM) - WRONG
TODAY (7 PM) - WRONG
TODAY (9 PM) - WRONG
PAST (3 DAYS AGO)
PAST (2 DAYS AGO)
PAST (1 DAYS AGO)
FUTURE (IN 1 DAY)
FUTURE (IN 2 DAYS)
FUTURE (IN 3 DAYS)

I tried changing the the "today" sorting using DESC:

IF(DATE(my_date) = DATE(NOW()), 0, 1) DESC,

But then it just sends the records to the very bottom (even below the future results). Is this possible to do with just SQL or will I have to run multiple queries using CodeIgniter's where() and merge them using PHP?

Upvotes: 3

Views: 4351

Answers (3)

spencer7593
spencer7593

Reputation: 108420

I think this will meet the specified requirement:

SELECT t.*
  FROM my_table t
 ORDER
    BY DATE(t.my_date)=DATE(NOW()) DESC
     , IF(DATE(t.my_date)=DATE(NOW()),t.my_date,DATE(NULL)) DESC
     , t.my_date ASC

Let's unpack that a little bit.

The first expression in the ORDER BY will evaluate to a 1 (if the conditional test is true), 0 (if it's false), or NULL (if one of the expressions is NULL.) We want descending order, to get the rows for which the condition is 'true' sorted first.

The second expression is an IF function. If the date portion of the my_date value is today's date, then we return my_date. We want that sorted in descending order (per the specification.) Otherwise, we want to return a constant, so that all the rest of the rows are going to be considered "equal".

Third, we order by the my_date value in ascending order. This won't affect the rows from today, those are already ordered in descending, so this really only affects the rows that are "equal" up to this point (which would be "not today"). Any rows with my_date value of NULL should wind up last (the first expression in the ORDER BY actually guarantees us that.)

Upvotes: 9

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Parado's answer separates the values correctly. But the ordering also varies by group. The values are descending for the current day, and then ascending for the other days. Here is one way:

SELECT * 
FROM my_table 
ORDER BY (case when DATE(my_date) = DATE(NOW())
               then my_date
               else date('2000-01-01')
          end
         ) desc,
         my_date asc;

The second two groups can be combined. When the dates are ordered in ascending order, then the past comes before the future.

EDIT:

The purpose of the `date('2000-01-01') is for NULL values (ie. not today) to go last. You can do this with three part logic:

SELECT * 
FROM my_table 
ORDER BY (case when DATE(my_date) = DATE(NOW()) then 1 else 2 end),
         (case when DATE(my_date) = DATE(NOW())
               then my_date
          end
         ) desc,
         my_date asc;

Upvotes: 4

Robert
Robert

Reputation: 25753

Try to use CASE syntax in order by clause and column my_date after that as below

SELECT * 
FROM my_table 
ORDER BY 
    CASE 
      WHEN DATE(my_date) = DATE(NOW()) THEN 0
      WHEN DATE(my_date) < DATE(NOW()) THEN 1
      WHEN DATE(my_date) > DATE(NOW()) THEN 2
      ELSE 3
    END , DATE(my_date) desc

Upvotes: 3

Related Questions