[Solved] The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML

by


The relational model specifies that the tuples of a relation have no specific order. In other words, rows in a database table have no specific order.
Therefor, when you are creating anything that can be references as a table (i.e views, derived tables etc’) you can’t specify an order by clause unless it’s needed for restricting the results (with the use of top or offset).
You can use the order by clause with the for xml clause, since creating an xml is not the same as creating a table.

Now that I’ve explained the reason behind the error message you get, here is the solution – Simply remove the order by clause from your derived table:

SELECT * FROM
      (SELECT H.Plotingan as 'Plotingan',
              E.EmployeeName as 'Nama',
              E.EmployeePosition as 'Jabatan',
              E.EmployeeNo as 'NIK',
              D.Tanggal,D.DutyCode
        FROM CobaDutyDetail D
        JOIN CobaDutyHeader H
          ON D.CobaDutyHeaderID = H.IDHeaderDuty
        JOIN Employee E
          ON H.EmployeeID = E.EmployeeID
       WHERE E.WorkLocation = 'JAKARTA'
       ) AS SOURCETABLE
       PIVOT(MAX(DutyCode)FOR TANGGAL IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])) AS PIVOTTABLE

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