CODEWITHSUNDEEP
phpmysqldatesql-order-by
Jimmy M
Jimmy M

Reputation: 1627

MySQL query to order records ORDER BY

This question is related to my previous question - MySQL query to show records with current date on top and others as per descending order . The query i uses now is,

SELECT b.sales_id,b.category_id,b.sale_starts,b.sale_ends 
FROM tbl_sales b WHERE b.active=1 
UNION
SELECT b.sales_id,b.category_id,b.sale_starts,b.sale_ends 
FROM tbl_sales b INNER JOIN tb_category c ON  b.category_id=c.cat_id 
WHERE c.cat_keyword LIKE 'a' 
ORDER BY IF(sale_ends = DATE(NOW()), 0, 1), sale_ends DESC

and the results returned is as follows,

sales_id      | category_id         |sale_starts | sale_ends 
    ----------|---------------------|------------|--------------
     4        |   12                | 2012-04-05 | 2012-04-11 (today's date)
     1        |   10                | 2012-03-31 | 2012-04-30     
     2        |   11                | 2012-03-22 | 2012-04-27
     3        |   25                | 2012-03-31 | 2012-04-25
     5        |   18                | 2012-04-05 | 2012-04-09
     6        |   20                | 2012-02-23 | 2012-02-27
     7        |   14                | 2012-02-25 | 2012-02-26

But now i am stuck with another issue and i need the to sort the records like shown below -

    sales_id      | category_id         |sale_starts | sale_ends 
        ----------|---------------------|------------|--------------
         4        |   12                | 2012-04-05 | 2012-04-11 (today's date)
         3        |   25                | 2012-03-31 | 2012-04-25
         2        |   11                | 2012-03-22 | 2012-04-27
         1        |   10                | 2012-03-31 | 2012-04-30 
         7        |   14                | 2012-02-25 | 2012-02-26 (expired/past dates)
         6        |   20                | 2012-02-23 | 2012-02-27
         5        |   18                | 2012-04-05 | 2012-04-09

I had tried using ASC instead of DESC in the query but then the expired dates are listed just after the today's date. I need the future dates to listed after the today's date and after that only the expired dates. How can this be implemented?

Need help. Thanks in advance

Upvotes: 0

Views: 313

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

I might be misunderstanding something, but isn't this what you're looking for?

order by sale_ends < curdate(), sale_ends

Fiddle here.

Upvotes: 1

liquorvicar
liquorvicar

Reputation: 6106

ORDER BY will process each condition in order from left to right. So if the ordering you need is something like "Any entries with today's date, then any entries in the future in ascending order of date, then other entries in ascending order of date" you can do something like this

ORDER BY (sale_ends=CURDATE()) DESC,(sale_ends>CURDATE()) DESC,sale_ends ASC

The reason the first two are listed as DESC is that the conditions will evaluate to one if true and zero if false. Since you want the true conditions first then you need to order them in DESCending order.

Upvotes: 3

Related Questions