SQLTeam.com | Weblogs | Forums

Sorting the results of a Union Query

Hello everybody,

I am using a Union Query to combine the results of 2 CTE's into a single data set. My query is working and I am getting the results that I expect. The only thing is that I would like to do an ORDER BY on my date column. Here is the code I have:

WITH CTE_Orders
AS
(
SELECT
o.Order_Number,
o.Order_Region,
o.Processed_Date
FROM orders o
),

CTE_Sales
AS
(
SELECT
s.Sales_Number,
s.Sales_Region,
s.Processed_Date
FROM Sales s
)

SELECT o.Order_Number, o.Order_Region, o.Processed_Date
FROM CTE_Orders o
UNION
ALL
SELECT s.Sales_Number, s.Sales_Region, s.Processed_Date
FROM CTE_Sales s

I know that I can't use Order BY in the CTE.

Is there a way of sorting the results of my query in date order?

I don't have much experience with union queries so any help would be much appreciated.

Many Thanks

Vinnie

hi

put the last union select into CTE and order by in that cte

SQL ....
;WITH cte_orders 
     AS (SELECT o.order_number, 
                o.order_region, 
                o.processed_date 
         FROM   orders o), 
     cte_sales 
     AS (SELECT s.sales_number, 
                s.sales_region, 
                s.processed_date 
         FROM   sales s), 
     ctefinal 
     AS (SELECT o.order_number, 
                o.order_region, 
                o.processed_date 
         FROM   cte_orders o 
         UNION ALL 
         SELECT s.sales_number, 
                s.sales_region, 
                s.processed_date 
         FROM   cte_sales s) 
SELECT * 
FROM   ctefinal 
ORDER  BY HERE

Put an order by at the end of your last select in the union:
Order By Processed_Date

1 Like

Thanks for your help. This works great.

Vinnie