0

What I would like to know is, if you could take two queries and get them orderly represented in one resultset.

So in example we take two queries:

Select a.col1, a.col2 from a where a.col3 = 1 Order By a.col2

and

Select a.col1, a.col2 from a where a.col4 = 0 Order By a col2

I know its very easy to combine both queries into one in this case:

Select a.col1, a.col2
from a
where a.col3 = 1 and a.col4 = 0 Order By a col2

But the order of the result rows will now be mixed between first and second query.

Whereas I'm looking for an order where I get first all the first query results and second all the second query results

Also I see you could easily solve this example by ordering by col3 and col4. But I hope you see this is no solution to many other conditions given.

Maybe there is no general way, but I don't know all the functions one could use and I'm not really advanced in writing up query commands

Thank you.

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • 1
    Your combination attempt is logically incorrect - do you see that? Do you also realize that you can have the same row appear in both resultsets? In such a case, what do you expect? And please don't just discard the idea that this will never happen. If you wish to assume that, then make that assumption explicit and document that in your query code (comments are useful) so that others can know your assumption if it fails. – SMor Sep 20 '21 at 22:28
  • Right the "and" must be an "or". If its possible a row that appears in both query results should appear two times in the combined result. If not I dont know what would happen with it, but i think either way it doesnt make any problems unless it makes a solution impossible. –  Sep 21 '21 at 16:43

1 Answers1

1

You have 2 options:

  1. Use a CASE expression to order by the filter condition - this works when the tables are the same and the conditions simple.
select a.col1, a.col2
from a
where a.col3 = 1 or a.col4 = 0
order by
  case when a.col3 = 1 then 1 else 0 end desc
  , a.col2;

As noted by dogyog, this where clause should use OR not AND to combine the 2 queries.

  1. Use UNION ALL
select a.col1, a.col2, 0 QueryNum
from a
where a.col3 = 1

union all

select a.col1, a.col2, 1 QueryNum
from a
where a.col4 = 0

order by QueryNum asc, a.col2;
Dale K
  • 25,246
  • 15
  • 42
  • 71