1

Goodday, Please check my query first.

SELECT *
FROM
( 
    SELECT DISTINCT row, a.tanggal, b.OutletCode, c.Nilai, a.Nip, b.Fullname,
        a.KodePenilaian, f.Description AS posisilama, d.ShortDesc AS posisibaru
    FROM penilaian_header a 
    LEFT JOIN Employee b
        ON a.Nip = b.Nip 
    LEFT JOIN Position f
        ON b.PositionCode = f.PositionCode 
    LEFT JOIN Position d
        ON a.PositionCode = d.PositionCode 
    LEFT JOIN arealeader g
        ON g.OutletCode = b.OutletCode 
    LEFT JOIN
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY KodePenilaianH
                                  ORDER BY idPenilaiand DESC) AS Row,
            Nilai, KodePenilaianH
        FROM penilaian_Detail
    ) c
        ON a.KodePenilaian = c.KodePenilaianH 
    WHERE a.Outlet LIKE '%%' AND Periode LIKE '%%'
    ORDER BY b.OutletCode ASC
) nilai PIVOT (SUM(nilai) FOR ROW IN ([1],[2],[3],[4],[5])) piv;

My problem is when i'm add Order by my query error. Here is the error :

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

Without Order By my query working fine.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
YVS1102
  • 2,658
  • 5
  • 34
  • 63

1 Answers1

2

I believe that ORDER BY b.OutletCode ASC is the cause of the error, rather than the ORDER BY inside the partition, which is necessary and should be allowed.

If you want to return all records, you can use TOP with a large number, e.g.

SELECT *
FROM
( 
    SELECT DISTINCT TOP 2147483647 row, a.tanggal, b.OutletCode, c.Nilai, a.Nip,
        b.Fullname, a.KodePenilaian, f.Description AS posisilama, d.ShortDesc AS posisibaru
    FROM penilaian_header a 
    LEFT JOIN Employee b
        ON a.Nip = b.Nip 
    ...
    ORDER BY b.OutletCode ASC
) nilai PIVOT (SUM(nilai) FOR ROW IN ([1],[2],[3],[4],[5])) piv;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360