CODEWITHSUNDEEP
sqlsql-servert-sqlunion
tree
tree

Reputation: 740

SQL - UNION, priority on the first select statement when doing order by

I'm trying to print out the results from the "GermanDB" Database first, while also showing everything from the Boston DB that was not in the German database. Can this be done in one query?

My query (the bold part functions but does not order the way I want)

select * from (  
SELECT DISTINCT a.ProductRef  
FROM GERMANDB.dbo.LOCATIONS AS a INNER JOIN GERMANDB.dbo.ITEMS AS b ON a.ProductRef =   b.ProductRef   
WHERE b.ACTIVE=1  
) ta  
UNION select * from  
SELECT DISTINCT c.ProductRef  
FROM BOSTONDB.dbo.LOCATIONS AS c INNER JOIN BOSTONDB.dbo.ITEMS AS d ON c.ProductRef =   d.ProductRef   
WHERE c.ACTIVE=1 (c.ProductRef NOT IN   
(SELECT ProductRef FROM GERMANDB.dbo.ITEMS where ACTIVE=1))  
) tb  
order by ta.ProductRef** , tb.productRef

Upvotes: 2

Views: 9152

Answers (1)

Eduardo Molteni
Eduardo Molteni

Reputation: 39413

Just add one field to signal the priority. Like this:

select *, 0 as Priority from (  
SELECT DISTINCT a.ProductRef  
FROM GERMANDB.dbo.LOCATIONS AS a INNER JOIN GERMANDB.dbo.ITEMS AS b ON a.ProductRef =   b.ProductRef   
WHERE b.ACTIVE=1  
) ta  
UNION select *, 1 as Priority from  
SELECT DISTINCT c.ProductRef  
FROM BOSTONDB.dbo.LOCATIONS AS c INNER JOIN BOSTONDB.dbo.ITEMS AS d ON c.ProductRef =   d.ProductRef   
WHERE c.ACTIVE=1 (c.ProductRef NOT IN   
(SELECT ProductRef FROM GERMANDB.dbo.ITEMS where ACTIVE=1))  
) tb  
order by Priority, ProductRef

Upvotes: 13

Related Questions