ORDER BY子句在子查询中无效报错，求技术解决方案

Hey there, let's get that SQL sorted out! The error you're seeing happens because SQL Server doesn't allow an ORDER BY clause in a derived table, subquery, or similar nested result set unless you also include TOP, OFFSET, or FOR XML. This is because these nested queries return unordered sets of data—adding an ORDER BY here doesn't serve any practical purpose, and the database engine blocks it to avoid confusion.

Looking at your query, the problem is the ORDER BY [ID] in your innermost subquery. That's the line causing the error, and it's actually unnecessary here since your outer ROW_NUMBER() function is already handling the sorting logic you need.

Corrected SQL Script #

SELECT * 
FROM ( 
    SELECT *,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Date] DESC) [Order] 
    FROM ( 
        SELECT A.[ID], B.[UID], C.[Date] 
        FROM Sample_1 A 
        FULL JOIN Sample_2 B ON A.[ID] = B.[ID] 
        FULL JOIN Sample_3 C ON A.[ID] = C.[ID] 
        WHERE A.[SourceFile] BETWEEN 1 AND 100
        -- Removed the problematic ORDER BY [ID] here
    ) a 
) b
-- Optional: Add this if you want the latest record per ID
WHERE [Order] = 1

Why This Works #

• We removed the ORDER BY [ID] from the innermost subquery—this was the root cause of the error, as it's invalid in that nested context.
• Your outer ROW_NUMBER() function still handles the critical sorting: it partitions by ID and orders each group by Date descending, which is likely the core logic you care about.

If for some edge case you did need to enforce an order in the innermost query (though this is almost never necessary for subsequent processing), you could add OFFSET 0 ROWS to make the ORDER BY valid, like this:

SELECT A.[ID], B.[UID], C.[Date] 
FROM Sample_1 A 
FULL JOIN Sample_2 B ON A.[ID] = B.[ID] 
FULL JOIN Sample_3 C ON A.[ID] = C.[ID] 
WHERE A.[SourceFile] BETWEEN 1 AND 100
ORDER BY [ID] OFFSET 0 ROWS

But again, this is unnecessary for your original query's intent—just removing the ORDER BY is the cleanest fix.

内容的提问来源于stack exchange，提问作者John