How to make a correct selection with double sorting?

Question

SQL

Hello!

The site displays products sorted by rating ( rating field ).
Products also have an availability field , which indicates the number of product leftovers, if the product is out of stock, then 0 is indicated accordingly .
Now we need to display all products sorted by rating, but for products that are not available ( availability = 0 ) to be at the end of the list.

So now the request looks like this:

SELECT ms2.*, ec.rating
FROM `rt_ms2_products` AS `ms2`
LEFT JOIN `rt_ms2_reviews` AS `ec` ON ms2.id=ec.id
GROUP BY ms2.id
ORDER BY ec.rating DESC
LIMIT 0, 20

I thought that such a request would solve the problem, but it turned out not. Products with availability=0 still appear in the middle of the list.

SELECT ms2.*, ec.rating
FROM `rt_ms2_products` AS `ms2`
LEFT JOIN `rt_ms2_reviews` AS `ec` ON ms2.id=ec.id
GROUP BY ms2.id
ORDER BY ec.rating DESC, ms2.availability DESC
LIMIT 0, 20

Accordingly, the question is: what kind of query to complete this task? Use UNION?

Answer 1

To solve the problem, you need to keep in mind that order by can not only work directly with table columns, but with any calculated expression within the properties of one resulting row.
In this case, you need to prioritize sorting by ms2.availability > 0 and ms2.availability = 0, but without taking into account the weight of the ms2.availability value.
You can use the expression ms2.availability = 0, which outputs true or false, which will be processed by order by.

SELECT ms2.*, ec.rating
FROM `rt_ms2_products` AS `ms2`
LEFT JOIN `rt_ms2_reviews` AS `ec` ON ms2.id=ec.id
GROUP BY ms2.id
ORDER BY ms2.availability = 0, ec.rating DESC
LIMIT 0, 20

However, if a DBMS comes across where it is impossible to work with a boolean type in order by, then case ... end will help out, the result of which will give 0 or 1 - this is a universal approach.

SELECT ms2.*, ec.rating
FROM `rt_ms2_products` AS `ms2`
LEFT JOIN `rt_ms2_reviews` AS `ec` ON ms2.id=ec.id
GROUP BY ms2.id
ORDER BY case when ms2.availability = 0 then 1 else 0 end, ec.rating DESC
LIMIT 0, 20