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