Custom Sort Order in CTE-CodePudding

I need to get a custom sort order in a CTE but the error shows

"--The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified."

What's a better way to get the custom order in the CTE?

WITH
ctedivisiondesc
as
(
SELECT * FROM (
  SELECT --TOP 1 --[APPID]
       DH1.[ID_NUM]
      --,[SEQ_NUM_2]
      --,[CUR_DEGREE]
      --,[NON_DEGREE_SEEKING]
      ,DH1.[DIV_CDE]
      ,DDF.DEGREE_DESC 'DivisionDesc'
      --,[DEGR_CDE]
      --,[PRT_DEGR_ON_TRANSC]
      --,[ACAD_DEGR_CDE]
      ,[DTE_DEGR_CONFERRED]
      --,MAX([DTE_DEGR_CONFERRED]) AS Date_degree_conferred
      ,ROW_NUMBER() OVER (
                         PARTITION BY [ID_NUM] 
                         ORDER BY [DTE_DEGR_CONFERRED] DESC --Getting last degree
                   ) AS [ROW NUMBER]

  FROM [TmsePrd].[dbo].[DEGREE_HISTORY] As DH1
      inner join [TmsePrd].[dbo].[DEGREE_DEFINITION] AS DDF
      on DH1.[DEGR_CDE] = DDF.[DEGREE]
      --ORDER BY 
                --DIV_CDE                       Level
                --CE    Continuing Education    
                --CT    Certificate             1
                --DC    Doctor of Chiropractic  4
                --GR    Graduate                3
                --PD    Pending Division    
                --UG    Undegraduate            2

        --The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
        ORDER BY CASE 
                   WHEN DDF.DEGREE_DESC = 'Certificate' THEN 1
                   WHEN DDF.DEGREE_DESC = 'Undegraduate' THEN 2
                   WHEN DDF.DEGREE_DESC = 'Graduate' THEN 3
                   WHEN DDF.DEGREE_DESC = 'Doctor of Chiropractic' THEN 4
                   ELSE 5
                 END 

  ) AS t
  WHERE [ROW NUMBER] <= 1
)
SELECT * FROM ctedivisiondesc

CodePudding user response：

You need to sort the outer query.

Sorting a subquery is not allowed because it is meaningless, consider this simple example:

WITH CTE AS
(    SELECT  ID
     FROM (VALUES (1), (2)) AS t (ID)
     ORDER BY ID DESC
 )
 SELECT *
 FROM CTE
 ORDER BY ID ASC;

The ordering on the outer query has overridden the ordering on the inner query rendering it a waste of time.

It is not just about explicit sorting of the outer query either, in more complex scenarios SQL Server may sort the subqueries any which way it wishes to enable merge joins or grouping etc. So the only way to guarantee the order or a result is to order the outer query as you wish.

Since you may not have all the data you need in the outer query, you may would probably need to create a further column inside the CTE to use for sorting. e.g.

WITH ctedivisiondesc AS
(
    SELECT  *
    FROM    (   SELECT  DH1.ID_NUM,
                        DH1.DIV_CDE,
                        DDF.DEGREE_DESC AS DivisionDesc,
                        DTE_DEGR_CONFERRED,
                        ROW_NUMBER() OVER (PARTITION BY ID_NUM ORDER BY DTE_DEGR_CONFERRED DESC) AS [ROW NUMBER],
                        CASE 
                           WHEN DDF.DEGREE_DESC = 'Certificate' THEN 1
                           WHEN DDF.DEGREE_DESC = 'Undegraduate' THEN 2
                           WHEN DDF.DEGREE_DESC = 'Graduate' THEN 3
                           WHEN DDF.DEGREE_DESC = 'Doctor of Chiropractic' THEN 4
                           ELSE 5
                         END AS SortOrder
                FROM    TmsePrd.dbo.DEGREE_HISTORY AS DH1
                        INNER JOIN TmsePrd.dbo.DEGREE_DEFINITION AS DDF
                            ON DH1.DEGR_CDE = DDF.DEGREE

            ) AS t
    WHERE   t.[ROW NUMBER] <= 1
)
SELECT  ID_NUM,
        DIV_CDE,
        DivisionDesc,
        DTE_DEGR_CONFERRED
FROM    ctedivisiondesc
ORDER BY SortOrder;