Sql Server : The Order By Clause Is Invalid In Views, Inline Functions, Derived Tables, Subqueries

I have this query in a stored procedure): SELECT * FROM dbo.JointHistory c OUTER APPLY (SELECT MAX(CASE NdtType WHEN 'RT' THEN RequestNumber END) AS MasterRTRe

Solution 1:

As the error says, you have an ORDER BY in the inner query.Since you are not displaying the id I don't think ordering it by id makes any sense. So you can remove the order by and convert the INNER Query to a CTE and do it like this:

;WITH ReportTable
AS(
   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
)


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  ReportTable  i)b
--ORDER BY b.Id DESC