MariaDB How to ORDER BY results by multiple fields using weighting?

Question

I have a products table on database toto, looking like this:

MariaDB [toto]> desc products;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI | NULL    |       |
| product_id | int(11)      | NO   |     | NULL    |       |
| sold_at    | datetime     | YES  |     | NULL    |       |
| rate       | int(11)      | YES  |     | NULL    |       |
| name       | varchar(128) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Admitting now I have theses data:

MariaDB [toto]> select * from products;
+----+------------+---------------------+----------+-----------+
| id | product_id | sold_at             | rate     | name      |
+----+------------+---------------------+----------+-----------+
|  1 |        400 | 2023-04-14 07:28:44 |     NULL | product_1 |
|  2 |        399 | NULL                |      728 | product_2 |
|  3 |        401 | 2023-04-14 07:28:44 |   100000 | product_3 |
|  4 |        398 | NULL                | 99999999 | product_4 |
|  5 |        404 | 2023-04-14 07:45:00 |        1 | product_5 |
+----+------------+---------------------+----------+-----------+
5 rows in set (0.001 sec)

My goal is to sort all those results by rate DESC, with all products having a sold_at value not null first, meanings I would like to have all my non null sold_at equal to 1 and null sold_at to 0. to looks like something like:

+----+------------+---------------------+----------+-----------+
| id | product_id | sold_at             | rate     | name      |
+----+------------+---------------------+----------+-----------+
|  3 |        401 | 2023-04-14 07:28:44 |   100000 | product_3 |
|  5 |        404 | 2023-04-14 07:45:00 |        1 | product_5 |
|  1 |        400 | 2023-04-14 07:28:44 |     NULL | product_1 |
|  4 |        398 | NULL                | 99999999 | product_4 |
|  2 |        399 | NULL                |      728 | product_2 |
+----+------------+---------------------+----------+-----------+

Note that some products may have a sold_at with a NULL rate.

I currently tried multiple approach but none worked, such as:

WITH data AS (SELECT * FROM products ORDER BY rate DESC) SELECT * FROM data ORDER BY sold_at;

---

SELECT * FROM products ORDER BY rate DESC, sold_at ASC;

I know why that doesn't works (because sold_at is currently being ordered too using it's date, but I don't know how to do it).

Answer 1

In MariaDB, we could phrase this as:

select *
from mytable
order by (sold_at is null), rate desc

In the context of an order by clause, predicate sold_at is null evaluates as 1 if true, and 0 if false - so this puts non-null values first.

id	product_id	sold_at	rate	name
3	401	2023-04-14 07:28:44	100000	product_3
5	404	2023-04-14 07:45:00	1	product_5
1	400	2023-04-14 07:28:44	null	product_1
4	398	null	99999999	product_4
2	399	null	728	product_2

fiddle