title: SQL Server : the ORDER BY clause is invalid in views, inline functions, derived tables, subqueries
body: <p>I have this query in a stored procedure):</p>
  
  <pre><code>SELECT *
  FROM   
      dbo.JointHistory c
  OUTER APPLY
      (SELECT 
           MAX(CASE NdtType WHEN 'RT' THEN RequestNumber END) AS MasterRTRequestNumber,
           MAX(CASE NdtType WHEN 'RT' THEN NdtReportNumber END) AS ContractorRTRequestNumber,
           MAX(CASE NdtType WHEN 'RT' THEN ResponseReportNumber END) AS ContractorRTReportNumber,
           MAX(CASE NdtType WHEN 'RT' THEN ResponseReportDatetime END) AS RTDate,
           MAX(CASE NdtType WHEN 'RT' THEN Defect END) AS RTDefect,
           MAX(CASE NdtType WHEN 'RT' THEN Remark END) AS RTSegment,
           MAX(CASE NdtType WHEN 'PT' THEN RequestNumber END) AS MasterPTRequestNumber,
           MAX(CASE NdtType WHEN 'PT' THEN NdtReportNumber END) AS ContractorPTRequestNumber,
           MAX(CASE NdtType WHEN 'PT' THEN ResponseReportNumber END) AS ContractorPTReportNumber,
           MAX(CASE NdtType WHEN 'PT' THEN ResponseReportDatetime END) AS PTDate,
           MAX(CASE NdtType WHEN 'PT' THEN Defect END) AS PTDefect,
           MAX(CASE NdtType WHEN 'PT' THEN Remark END) AS PTSegment,
           MAX(CASE NdtType WHEN 'PWHT' THEN RequestNumber END) AS MasterPWHTRequestNumber,
           MAX(CASE NdtType WHEN 'PWHT' THEN NdtReportNumber END) AS ContractorPWHTRequestNumber,
           MAX(CASE NdtType WHEN 'PWHT' THEN ResponseReportNumber END) AS ContractorPWHTReportNumber,
           MAX(CASE NdtType WHEN 'PWHT' THEN ResponseReportDatetime END) AS PWHTDate,
           MAX(CASE NdtType WHEN 'PWHT' THEN Defect END) AS PWHTDefect,
           MAX(CASE NdtType WHEN 'PWHT' THEN Remark END) AS PWHTSegment,
           MAX(CASE NdtType WHEN 'MT' THEN RequestNumber END) AS MasterMTRequestNumber,
           MAX(CASE NdtType WHEN 'MT' THEN NdtReportNumber END) AS ContractorMTRequestNumber,
           MAX(CASE NdtType WHEN 'MT' THEN ResponseReportNumber END) AS ContractorMTReportNumber,
           MAX(CASE NdtType WHEN 'MT' THEN ResponseReportDatetime END) AS MTDate,
           MAX(CASE NdtType WHEN 'MT' THEN Defect END) AS MTDefect,
           MAX(CASE NdtType WHEN 'MT' THEN Remark END) AS MTSegment,
           MAX(CASE NdtType WHEN 'UT' THEN RequestNumber END) AS MasterUTRequestNumber,
           MAX(CASE NdtType WHEN 'UT' THEN NdtReportNumber END) AS ContractorUTRequestNumber,
           MAX(CASE NdtType WHEN 'UT' THEN ResponseReportNumber END) AS ContractorUTReportNumber,
           MAX(CASE NdtType WHEN 'UT' THEN ResponseReportDatetime END) AS UTDate,
           MAX(CASE NdtType WHEN 'UT' THEN Defect END) AS UTDefect,
           MAX(CASE NdtType WHEN 'UT' THEN Remark END) AS UTSegment
       FROM   
           (SELECT TOP 1 WITH TIES 
                NRD.NdtType, NRD.RequestNumber, NR.NdtReportNumber,
                NRD.ResponseReportDatetime, NRD.Defect,
                NRD.ResponseReportNumber, NRD.Remark
            FROM   
                dbo.NdtReportDetails NRD
            LEFT OUTER JOIN 
                NdtReports NR ON NRD.ReportId = NR.Id
            WHERE  
                NRD.JointId = c.Id
                AND NRD.NdtType IN ( 'RT', 'PT', 'PWHT', 'MT', 'UT' )
            ORDER BY 
                NRD.Id DESC) i) b
  </code></pre>
  
  <p>But the end of query had a problem based on this question <a href="https://stackoverflow.com/questions/41518618/select-top-ties-in-sql-cant-return-expected-data/41518699">:https://stackoverflow.com/questions/41518618/select-top-ties-in-sql-cant-return-expected-data/41518699</a></p>
  
  <p>So my query is changed to this :</p>
  
  <pre><code> SELECT *
  FROM   dbo.JointHistory c
         OUTER Apply (SELECT Max(CASE NdtType WHEN 'RT' THEN RequestNumber END) AS MasterRTRequestNumber,
                             Max(CASE NdtType WHEN 'RT' THEN NdtReportNumber END) AS ContractorRTRequestNumber,
                             Max(CASE NdtType WHEN 'RT' THEN ResponseReportNumber END) AS ContractorRTReportNumber,
                             Max(CASE NdtType WHEN 'RT' THEN ResponseReportDatetime END) AS RTDate,
                             Max(CASE NdtType WHEN 'RT' THEN Defect END) AS RTDefect,
                             Max(CASE NdtType WHEN 'RT' THEN Remark END) AS RTSegment,
  
                             Max(CASE NdtType WHEN 'PT' THEN RequestNumber END) AS MasterPTRequestNumber,
                             Max(CASE NdtType WHEN 'PT' THEN NdtReportNumber END) AS ContractorPTRequestNumber,
                             Max(CASE NdtType WHEN 'PT' THEN ResponseReportNumber END) AS ContractorPTReportNumber,
                             Max(CASE NdtType WHEN 'PT' THEN ResponseReportDatetime END) AS PTDate,
                             Max(CASE NdtType WHEN 'PT' THEN Defect END) AS PTDefect,
                             Max(CASE NdtType WHEN 'PT' THEN Remark END) AS PTSegment,
  
                                 Max(CASE NdtType WHEN 'PWHT' THEN RequestNumber END) AS MasterPWHTRequestNumber,
                             Max(CASE NdtType WHEN 'PWHT' THEN NdtReportNumber END) AS ContractorPWHTRequestNumber,
                             Max(CASE NdtType WHEN 'PWHT' THEN ResponseReportNumber END) AS ContractorPWHTReportNumber,
                             Max(CASE NdtType WHEN 'PWHT' THEN ResponseReportDatetime END) AS PWHTDate,
                             Max(CASE NdtType WHEN 'PWHT' THEN Defect END) AS PWHTDefect,
                             Max(CASE NdtType WHEN 'PWHT' THEN Remark END) AS PWHTSegment,
  
                                     Max(CASE NdtType WHEN 'MT' THEN RequestNumber END) AS MasterMTRequestNumber,
                             Max(CASE NdtType WHEN 'MT' THEN NdtReportNumber END) AS ContractorMTRequestNumber,
                             Max(CASE NdtType WHEN 'MT' THEN ResponseReportNumber END) AS ContractorMTReportNumber,
                             Max(CASE NdtType WHEN 'MT' THEN ResponseReportDatetime END) AS MTDate,
                             Max(CASE NdtType WHEN 'MT' THEN Defect END) AS MTDefect,
                             Max(CASE NdtType WHEN 'MT' THEN Remark END) AS MTSegment,
  
                                             Max(CASE NdtType WHEN 'UT' THEN RequestNumber END) AS MasterUTRequestNumber,
                             Max(CASE NdtType WHEN 'UT' THEN NdtReportNumber END) AS ContractorUTRequestNumber,
                             Max(CASE NdtType WHEN 'UT' THEN ResponseReportNumber END) AS ContractorUTReportNumber,
                             Max(CASE NdtType WHEN 'UT' THEN ResponseReportDatetime END) AS UTDate,
                             Max(CASE NdtType WHEN 'UT' THEN Defect END) AS UTDefect,
                             Max(CASE NdtType WHEN 'UT' THEN Remark END) AS UTSegment
  
  
                      FROM   (
  
  SELECT t.NdtType,
         t.RequestNumber,
         t.NdtReportNumber,
         t.ResponseReportDatetime,
         t.Defect,
         t.ResponseReportNumber,
         t.Remark
  FROM
  (
      SELECT NRD.NdtType,
             NRD.Id,
             NRD.RequestNumber,
             NR.NdtReportNumber,
             NRD.ResponseReportDatetime,
             NRD.Defect,
             NRD.ResponseReportNumber,
             NRD.Remark,
             ROW_NUMBER() OVER(PARTITION BY NRD.NdtType ORDER BY NRD.Id DESC) AS rn
      FROM dbo.NdtReportDetails NRD
      LEFT OUTER JOIN NdtReports NR
          ON NRD.ReportId = NR.Id
      WHERE NRD.JointId = c.Id AND
            NRD.NdtType IN ('RT', 'PT', 'PWHT', 'MT', 'UT')
  ) t
  WHERE t.rn = 1
  ORDER BY t.Id DESC
  
  ) i)b
  </code></pre>
  
  <p>As you can see the end of query is changed based on above question, after I execute this query, I get this error:</p>
  
  <blockquote>
    <p>Msg 1033, Level 15, State 1, Procedure SPJointHistory, Line 72<br>
    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.</p>
  </blockquote>