SQL Query: Order by in UNION

Question

I am having a bit of 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 2 ways, the first way is orders that are urgent (so prio 6 or 16) and the second way, the remaining orders sort by Prio.

So this is what my table looks like :

ORDNR  PRIO
1       6
2       16
3       2
4       8

What I want is this:

ORDNR  PRIO
2       16
1       6
4       8
3       2

My attempt for a query was this:

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 error from SQL: A syntax error near 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