SQL Order results in UNION and make them appear in the order UNION statements-CodePudding

I can not come up with syntax to get the results in order with unions. What I am after is that if the first union has a result, it must appear first, if second one has a result, it must appear second and so on so forth..

select a.id, a.id2 
from Table1 a
where   a.id3=(select c1.id1 from table c1 where c1.name='A') --Must be first result
UNION
select distinct a.id, a.id2 
from Table1 a
where   a.id3=(select c2.id2 from table c2 where c2.name='B')--Must be second result (if exists)
UNION
select distinct a.id, a.id2 
from Table1 a
where   a.id3=(select c3.id3 from table c3 where c3.name='C')--Must be 3rd result (if exists)

Now if second union does not have a result, the 3rd one will be second.. Can some one please guide?

CodePudding user response：

I feel like you've significantly overly complicated this. Can't you just JOIN to table and ORDER BY its name column?

SELECT T1.id,
       T1.id2
FROM dbo.Table1 T1
     JOIN dbo.[Table] T ON T1.id3 = T.id
WHERE T.Name IN ('A','B','C')
ORDER BY T.Name ASC;

If you could end up with multiple JOINs then you can use aggregation, rather than a DISTINCT:

SELECT T1.id,
       T1.id2
FROM dbo.Table1 T1
     JOIN dbo.[Table] T ON T1.id3 = T.id
WHERE T.Name IN ('A','B','C')
GROUP BY T1.id,
         T1.id2
ORDER BY MIN(T.Name) ASC;

CodePudding user response：

Larnu's answer is definitely superior to your unions (and you probably want UNION ALL rather than UNION anyway, unless your data model really is messed up enough that you have all of those duplicates all over the place).

However a simple approach to keep in mind when using UNION/UNION ALL is like this:

SELECT [set] = 1, ... FROM dbo.table1
UNION ALL
SELECT [set] = 2, ... FROM dbo.table2
UNION ALL
SELECT [set] = 3, ... FROM dbo.table3
ORDER BY [set], ...;

This puts an ordering column in each set and allows you to control the order first by that column and then by anything else.

CodePudding user response：

The UNION does not guarantee the order of its groups. The SQL engine will group the result set and then it will return in a random order.

In order to force an order based on your sql input, you can either ORDER BY column id or id2 if that is relevant. Otherwise, you should add another column to explicitly order the result set:

SELECT id, id2 FROM (
select a.id, a.id2, 1 as customOrder 
from Table1 a
where   a.id3=(select c1.id1 from table c1 where c1.name='A') --Must be first result
UNION
select distinct a.id, a.id2, 2 as customOrder 
from Table1 a
where   a.id3=(select c2.id2 from table c2 where c2.name='B')--Must be second result (if exists)
UNION
select distinct a.id, a.id2, 3 as customOrder 
from Table1 a
where   a.id3=(select c3.id3 from table c3 where c3.name='C')--Must be 3rd result (if exists)
) src 
ORDER BY src.customOrder