Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL query to show records with current date on top and others as per descending order

I am using the following query in my database,

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 sale_ends  DESC

and getting the result as follows,

sales_id  | category_id         |sale_starts | sale_ends 
----------|---------------------|------------|--------------
 1        |   10                | 2012-03-31 | 2012-04-30     
 2        |   11                | 2012-03-22 | 2012-04-27
 3        |   25                | 2012-03-31 | 2012-04-25
 4        |   12                | 2012-04-05 | 2012-04-11

Now i need to get the result as follows, ie the row which has today's date/current date assale_ends must be shown in the top of the order (assuming today's date/current date is 2012-04-11), like shown below-

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

Need help in this, thanks in advance.

like image 344
Jimmy M Avatar asked Feb 20 '23 20:02

Jimmy M


1 Answers

Try this ORDER BY clause with condition -

ORDER BY IF(sale_ends = DATE(NOW()), 0, 1), sale_ends DESC
like image 78
Devart Avatar answered Apr 25 '23 05:04

Devart