Question 114813 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to concatenate multiple queries in a single query?

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:

id score cond timestamp
item-1 37.5 true …
item-2 40.4 true …
item-3 12.3 false yesterday
item-4 55.9 true …
item-5 20.1 false 1 week ago

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:

id score cond timestamp
item-3 12.3 false yesterday
item-5 20.1 false 1 week ago
item-4 55.9 true …
item-2 40.4 true …
item-1 37.5 true …

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

Tags:

2

Answers


  1. 0

    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, case when cond = 'false' then "timestamp"  end desc, score
    id score cond timestamp
    item-3 12.3 false yesterday
    item-5 20.1 false 1 week ago
    item-4 55.9 true …
    item-2 40.4 true …
    item-1 37.5 true …

    Fiddle

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search