TOP Clause

The number of results returned by a query can be limited by TOP, followed by an expression with a non-negative integer.

SELECT TOP 100 [r]
FROM [invoice] AS [r]

The ORDER BY clause rarely makes sense without a TOP clause. If you would like your query to return the 100 invoices with the highest amounts, this can be formulated in the following way.

SELECT TOP 100 [r]
FROM [invoice] AS [r]
ORDER BY [amount] DESC

More complex expressions are also possible. All invoices except the 100 with the lowest amount can therefore be selected.

SELECT TOP (SELECT count(*)-100 FROM [invoice]) [r]
FROM [invoice] AS [r]
ORDER BY [amount] DESC

The statement only works if there are at least 100 invoices, i.e., the expression after TOP is a non-negative number.

The TOP clause is only possible in outermost queries, not in subselects and not in partial queries linked by UNION or EXCEPT.