How to make a request, if possible?

Question

PostgreSQL

You must select the 10 highest rated entries from a particular category:

SELECT * FROM `tbl` WHERE `category` = 1 ORDER BY `rating` DESC LIMIT 10;

In this case, if there are no records that meet the condition, or there are not enough of them, then select the records with the highest rating from any category. Basically the same query, but without the WHERE block.

In general, there should be such an algorithm:
If the selection with WHERE gives us 10 records, then we select them and only them.
If there are no records with regard to WHERE, then we take 10 records from any categories.
If, taking into account WHERE, we can get only 4 records, then we take them, and finish off the remaining 6 with records from any categories.
But at the output, we should always get 10 entries sorted by rating.
Is it possible to write this in one request?

Answer 1

You can use sorting first by category = 1 DESC then by rating DESC

SELECT * FROM tbl 
ORDER BY 
  category = 1 DESC, 
  rating DESC 
LIMIT 10;