Mysql multiple order, one order by date with condition

I have a simple SQL request which orders the result by the number of sales and then by the number of views.

SELECT io__image.sales, io__image.viewcount, io__image.date 
FROM io__image
ORDER BY io__image.sales DESC, 
         io__image.viewcount DESC;

But I would like the new images with a date greater than for example “2022-05-01” to appear just after the ones that have been sold.

Is it possible to have multiple order and in one of this order a condition?

ORDER BY io__image.sales DESC, 
         if(io_image.date >= "2022-05-01") ..., 
         io__image.viewcount DESC;

Example of current results:

+-------+-----------+---------------------+
| sales | viewcount | date                |
+-------+-----------+---------------------+
|     5 |         7 | 2021-04-21 19:13:21 |
|     4 |       186 | 2018-05-09 13:45:40 |
|     4 |       135 | 2018-05-11 17:22:30 |
|     3 |       157 | 2018-05-02 09:47:48 |
|     1 |         8 | 2021-08-29 11:22:55 |
|     1 |         7 | 2021-06-21 12:26:32 |
|     1 |         5 | 2021-06-21 12:40:38 |
|     1 |         4 | 2021-06-14 15:15:01 |
|     0 |       824 | 2021-04-21 22:12:48 |
|     0 |       430 | 2020-11-27 13:46:59 |
|     0 |       228 | 2017-10-24 09:05:40 |
|     0 |       209 | 2019-11-24 11:32:43 |
|     0 |       184 | 2018-05-02 21:26:40 |
|     0 |       174 | 2018-05-02 21:21:20 |
|     0 |       174 | 2018-05-03 09:08:53 |
|     0 |       171 | 2018-05-02 09:20:34 |

Let’s say we have 2 new images with a date >= 2022-05-01 with low viewcount and no sales, and I would like:

+-------+-----------+---------------------+
| sales | viewcount | date                |
+-------+-----------+---------------------+
|     5 |         7 | 2021-04-21 19:13:21 |
|     4 |       186 | 2018-05-09 13:45:40 |
|     4 |       135 | 2018-05-11 17:22:30 |
|     3 |       157 | 2018-05-02 09:47:48 |
|     1 |         8 | 2021-08-29 11:22:55 |
|     1 |         7 | 2021-06-21 12:26:32 |
|     1 |         5 | 2021-06-21 12:40:38 |
|     1 |         4 | 2021-06-14 15:15:01 |

|     0 |        10 | 2022-07-14 12:11:25 |
|     0 |         5 | 2022-06-21 08:45:43 |

|     0 |       824 | 2021-04-21 22:12:48 |
|     0 |       430 | 2020-11-27 13:46:59 |
|     0 |       228 | 2017-10-24 09:05:40 |
|     0 |       209 | 2019-11-24 11:32:43 |
|     0 |       184 | 2018-05-02 21:26:40 |
|     0 |       174 | 2018-05-02 21:21:20 |
|     0 |       174 | 2018-05-03 09:08:53 |
|     0 |       171 | 2018-05-02 09:20:34 |

Answer

You can use the boolean expression sales = 0 AND date >= '2022-05-01' in the ORDER BY clause, between the 2 columns:

SELECT *
FROM io__image
ORDER BY sales DESC, 
         sales = 0 AND date >= '2022-05-01' DESC,
         viewcount DESC;

See the demo.

Advertisement

Answer