SQL Query: Sort by UNION

Question

I have a problem with SQL Server 2005 (Express Edition) with a UNION query.

I have this table Orders with the following columns: OrdNr, Prio Now I want to order by orders in two ways, the first way is urgent orders (so prio is 6 or 16), and the second way, the rest of the orders are sorted by Prio.

So this is what my table looks like:

ORDNR PRIO 1 6 2 16 3 2 4 8 

I want it:

 ORDNR PRIO 2 16 1 6 4 8 3 2 

My query attempt was as follows:

 SELECT OrdNbr, Prio FROM Orders WHERE Prio IN (6,16) ORDER BY Prio DESC UNION SELECT OrdNbr, Prio FROM Orders WHERE Prio NOT IN (6,16) ORDER BY Prio DESC 

But I get an SQL error message: Syntax error next to UNION

Please help: D

Answer 1

 SELECT OrdNbr, Prio FROM Orders ORDER BY CASE Prio WHEN 16 THEN 0 WHEN 6 THEN 1 ELSE 2 END, Prio DESC 

Answer 2

I think you need this

 SELECT OrdNbr, Prio FROM Orders WHERE Prio IN (6,16) UNION SELECT OrdNbr, Prio FROM Orders WHERE Prio NOT IN (6,16) ORDER BY Prio DESC 

== edit == if your Prio field is integer, I think the following will work

 select * from ( SELECT OrdNbr,Prio FROM Orders WHERE Prio IN (6,16) UNION SELECT OrdNbr, Prio FROM Orders WHERE Prio NOT IN (6,16) ) ORDER BY Prio DESC 

Answer 3

The easiest way (if you don't mind adding another output column):

 SELECT OrdNbr, Prio, 1 AS Seq FROM Orders WHERE Prio IN (6,16) UNION SELECT OrdNbr, Prio, 2 AS Seq FROM Orders WHERE Prio NOT IN (6,16) ORDER BY Seq, Prio DESC; 

Answer 4

Try the following:

 SELECT OrdNbr, Prio FROM ( SELECT OrdNbr, Prio, CASE WHEN Prio IN (6,16) THEN 0 ELSE 1 END ord FROM Orders ) t ORDER BY ord, Prio; 

Answer 5

SELECT ordnbr, PRIO,
Case prio
WHEN 16 THEN 1
WHEN 6 THEN 2
ELSE 3
END AS NewPriority
FROM orders
ORDER by newpriority, prio DESC

In accordance with the specified requirement 16 and 6 receive the first 2 priorities, and the remaining Orders should be sorted based on Prio

Only you will see an additional column (NewPriority), which can be masked during display in the application.

Plus, here's a tip if an application is built based on this, Ordernumber (OrdNbr) should be unique.