Sorting the results of a Union Query

1

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

2

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
3

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

1 Like
4

Thanks for your help. This works great.

Vinnie