Order two parts of a union independently of one another

user_937440 0
Viewed 98

Is it possible to do something like this:

select name from table1 order by name
union
select name from table2 order by name

I know I can do this:

select name from table1
union
select name from table2 order by name

However, I want the names from table1 to appear first. I have spent the last hour Googling this and I have go nowhere. For example, I have looked here: How to order by with union in SQL?

sql-server sql
3 Answers

The query needs to be a bit more complicated:

select name
from ((select distinct name, 1 as is_1 from table1) 
      union
      (select distinct name, 0 from table2)
     ) n
group by name
order by max(is_1), name;

This uses select distinct in the subqueries because that can take advantage of an index on name.

user_11440350

Add a "sort" field and put the union inside a subquery so you can sort after the union.

untested

select a.name
from (
   select name, 1 sort
   from table1

   union all

   select name, 2 sort
   from table2
) a
order by a.sort, a.name

I changed it to union all to make it clear this approach won't do a union. You could also select the sort column if you want to see it. If you don't want duplicate names, then this approach won't work.

user_120554790

You need another column to sort on. UNION does not allow the individual queries to have an ORDER BY clause.

Adding in a column to sort on before name allows for it to sort the individual result sets. See my example below:

CREATE TABLE #Table1 (Name VARCHAR(50))
CREATE TABLE #Table2 (Name VARCHAR(50))

INSERT INTO #Table1 VALUES ('Bart'), ('Lisa'), ('Maggie')
INSERT INTO #Table2 VALUES ('Chris'), ('Meg'), ('Stewie')

SELECT Name, 0 AS Sort FROM #Table1
UNION
SELECT Name, 1 AS Sort FROM #Table2
ORDER BY Sort, Name
user_18422730
Related