Search code examples
sqlsql-servert-sqlsyntaxmulti-query

Stack query results in one query result so the order of rows depends on which part returns which result

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.

Solution

  • 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;