Wierd Syntax Error when working with Order By clause with Union All in Toad for Sql Server 2016

Question

I want Union of two result set of periods. First set contains periods whose payroll is closed and the second set having only one period which is active. So I do order by in the second set to derive the active period in ascending order. But I always get "Incorrect Syntax near Union Keyword". I'm unable to trace what's happening around this.

SELECT SysId AS PeriodId,
       [Name] AS PeriodName,
       Number AS PeriodNumber,
       [Year] AS PeriodYear,
       PayrollSetId AS PayrollSetId
  FROM (SELECT p.SysId AS PeriodId,
               p.[Name] AS PeriodName,
               p.Number AS PeriodNumber,
               p.[Year] AS PeriodYear,
               p.PayrollSetId AS PayrollSetId
          FROM PeriodPayrollSetLookupView p
         WHERE p.Closed = 1
        UNION ALL
        SELECT TOP 1
               p.SysId AS PeriodId,
               p.[Name] AS PeriodName,
               p.Number AS PeriodNumber,
               p.[Year] AS PeriodYear,
               p.PayrollSetId AS PayrollSetId
          FROM PeriodPayrollSetLookupView p
         WHERE isnull (p.Closed, 0) = 0
        ORDER BY p.[Year], p.[Name]  
        UNION ALL
        SELECT '00000000-0000-0000-0000-000000000000' AS PeriodId,
       '<Select...>' AS PeriodName,
       NULL AS PeriodNumber,
       NULL AS PeriodYear,
       '00000000-0000-0000-0000-000000000000' AS PayrollSetId)AS PERIOD
ORDER BY [Year] DESC, Number DESC

A help is greatly appreciated.

Answer 1

You cannot use ORDER BY in a select that is used in a UNION. You could rewrite it using CTE's:

WITH closed AS (SELECT p.SysId AS periodid,
                   p.[Name] AS periodname,
                   p.Number AS periodnumber,
                   p.[year] AS periodyear,
                   p.PayrollSetId AS payrollsetid
              FROM PeriodPayrollSetLookupView p
             WHERE p.closed = 1),
 active AS (SELECT TOP 1
                   p.SysId AS periodid,
                   p.[Name] AS periodname,
                   p.Number AS periodnumber,
                   p.[year] AS periodyear,
                   p.PayrollSetId AS payrollsetid
              FROM PeriodPayrollSetLookupView p
             WHERE isnull (p.closed, 0) = 0
            ORDER BY p.[year], p.[name]),
 x AS (SELECT '00000000-0000-0000-0000-000000000000' AS periodid,
              '<select...>' AS periodname,
              NULL AS periodnumber,
              NULL AS periodyear,
              '00000000-0000-0000-0000-000000000000' AS payrollsetid)
SELECT *
  FROM (SELECT periodid,
               periodname,
               periodnumber,
               periodyear,
               payrollsetid
          FROM closed
        UNION ALL
        SELECT periodid,
               periodname,
               periodnumber,
               periodyear,
               payrollsetid
          FROM active
        UNION ALL
        SELECT periodid,
               periodname,
               periodnumber,
               periodyear,
               payrollsetid
          FROM x) t
ORDER BY periodyear DESC, periodnumber DESC