CODEWITHSUNDEEP
sqlsql-server
Emilio Sanches
Emilio Sanches

Reputation: 41

Can't use ORDER BY in a derived table

I am trying to select the last 20 rows of my SQL Database, but I get this error:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

My query is:

SELECT TOP 20 * FROM (SELECT * FROM TBArticles ORDER BY id_art DESC)

I think it's because I am using ORDER BY in this second expression... but what can I do to select the 20 last rows fixing this error?

Upvotes: 0

Views: 2058

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Gordon's answer is probably the most direct way to handle your requirement. However, if you wanted to use a query along the same lines as the pattern you were already using, you could use ROW_NUMBER here:

SELECT *
FROM
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id_art DESC) rn
    FROM TBArticles
) t
WHERE rn <= 20;

By computing a row number in the derived table, the ordering "sticks" in the same way your original query was expecting.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You don't need a subquery for this:

SELECT TOP 20 *
FROM TBArticles
ORDER BY id_art DESC

The documentation is quite clear on the use of ORDER BY in subqueries:

The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Upvotes: 2

Related Questions