Order two parts of a union independently of one another

Question

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?

Do you want `UNION` or `UNION ALL`, i.e. are there duplicates you need to remove? If so and a name exists in table 1 and table 2 where do you want that name in your results? — Thorsten Kettner, May 14 '20 at 14:41

Gordon Linoff · Answer 1 · 2020-05-14T15:25:23.763

3

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.

edited May 14 '20 at 15:25

answered May 14 '20 at 14:38

Gordon Linoff

1,242,037
58
646
786

Is there a benefit to using a sub query for this instead of just one query like in my example? They both produce the desired result. Just curious if there is something I am not seeing. – Chris Albert May 14 '20 at 14:58
@ChrisAlbert . . . You have to include an extra column in the result set, which the OP does not seem to want. – Gordon Linoff May 14 '20 at 15:00
@ChrisAlbert This solution will also eliminate duplicate names where our solutions will not. – avery_larry May 14 '20 at 15:11
1

@GordonLinoff I think you need a table alias for the subquery (before the `group by`). – avery_larry May 14 '20 at 15:12

score 2 · Answer 2 · answered May 14 '20 at 14:40

2

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.

answered May 14 '20 at 14:40

avery_larry

2,069
1
5
17

I was working on the same solution, but you posted first. I cleverly closed the SQL Fiddle without saving it, but this does work, within the noted limitations. – Eric Brandt May 14 '20 at 15:05

Chris Albert · Answer 3 · 2020-05-14T14:58:25.053

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

Order two parts of a union independently of one another

3 Answers3