Given the table below, I wonder if it's possible to create a single query (for performance reasons) that provides the result I am looking for. Here is an example of such table:

The result that I am looking for is the list of ids ordered by the following criteria:

• First, the ids where cond is False, ordered by timestamp, where the newest ones come first. For this query, I am able to use something like:
  • SELECT id FROM table WHERE cond is false ORDER BY timestamp
• Second, the ids where cond is True, ordered by score, where the highest score comes first. For this query, I am able to use something like:
  • SELECT id FROM table WHERE cond is true ORDER BY score DESC

The resulting table would be something like this:

What should I do to have the results of both queries in a single query? Thx

CodePudding user response：

Try to use "UNION ALL".

select * from 
(
  (SELECT id FROM table WHERE cond is false ORDER BY timestamp)
   UNION ALL
  (SELECT id FROM table WHERE cond is true ORDER BY score DESC)
) as newtable 

CodePudding user response：

It organizes the table by the order you put it in the order by clause. First the condition (false then true), then date and after that score.

select   *
from     t
order by cond, timestamp desc, score desc

Fiddle