Combining two SQL SELECT statements on the same table

Question

I would like to combine these two SQL queries:

SELECT * FROM "Contracts" WHERE 
"productType" = 'RINsell' AND
"clearTime" IS NULL AND
"holdTime" IS NOT NULL 
 ORDER BY "generationTime";

and

SELECT * FROM "Contracts" WHERE 
"productType" = 'RINsell' AND
"clearTime" IS NULL AND
"holdTime" IS NULL 
 ORDER BY "contractLimitPrice";

When I run each statement, I get exactly the results I want, I would just like both results sequentially. My first thought was to use UNION ALL since this selections will be disjoint but I found that you can't use a UNION after an ORDER BY. I've searched quite a bit and most people suggest doing the ORDER BY after the UNION but each query has different ORDER BY conditions.

Answer 1

If you want the results of the first query before the results of the second, you can remove holdtime from the where clause, and use an order by like

order by
  case when holdTime is not null then 0 else 1 end, --first query comes first
  case when holdTime is not null --different orders for queries
       then generationTime
       else contractLimitPrice
  end

Answer 2

... but I found that you can't use a UNION after an ORDER BY.

Well, you didn't look hard enough:

(
SELECT *
FROM   "Contracts"
WHERE  "productType" = 'RINsell'
AND    "clearTime" IS NULL
AND    "holdTime" IS NOT NULL 
ORDER  BY "generationTime"
)
UNION ALL
)
SELECT *
FROM   "Contracts"
WHERE  "productType" = 'RINsell'
AND    "clearTime" IS NULL
AND    "holdTime" IS NULL 
ORDER  BY "contractLimitPrice"
)

Note the parentheses. Per documentation:

(ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Closely related answer:
Sum results of a few queries and then find top 5 in SQL

Aside: I really would get rid of those CaMeL case identifiers. Your life is much easier with all-lower case legal identifiers in Postgres.