YVS1102 YVS1102 - 5 months ago 7
SQL Question

Error when adding order by

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.

Answer

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;
Comments