CODEWITHSUNDEEP
sqlsql-order-byunion
darkownage
darkownage

Reputation: 938

SQL Query: Order by in UNION

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

Upvotes: 4

Views: 5579

Answers (5)

Bharat Ram V
Bharat Ram V

Reputation: 101

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

As per the requirement given, 16 and 6 get the first 2 priorities and the remaining Orders should be sorted based on Prio

Only thing is you will be seeing an extra column(NewPriority) which can be masked while displaying in your application.

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

Upvotes: 0

nvogel
nvogel

Reputation: 25534

Try this:

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;

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97841

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

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;

Upvotes: 1

Prashant Lakhlani
Prashant Lakhlani

Reputation: 5806

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

== edited== if your Prio field is integer, i think 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

Upvotes: 2

ain
ain

Reputation: 22759

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

Upvotes: 6

Related Questions