Categories:: Query syntax

TOP <n>¶

Constrains the maximum number of rows returned by a statement or subquery.

See also:: LIMIT / FETCH

Syntax¶

SELECT
    [ TOP <n> ]
    ...
FROM ...
[ ORDER BY ... ]
[ ... ]

Copy

Parameters¶

n: The maximum number of rows to return in the result set.

Usage notes¶

An ORDER BY clause is not required; however, without an ORDER BY clause, the results are non-deterministic because results within a result set are not necessarily in any particular order. To control the results returned, use an ORDER BY clause.
n must be a non-negative integer constant.
TOP n and LIMIT count are equivalent.

Examples¶

The following example shows the effect of TOP N. For simplicity, these queries omit the ORDER BY clause and assume that the output order is always the same as shown by the first query. Real-world queries should include ORDER BY.

select c1 from testtable;

+------+
|   C1 |
|------|
|    1 |
|    2 |
|    3 |
|   20 |
|   19 |
|   18 |
|    1 |
|    2 |
|    3 |
|    4 |
| NULL |
|   30 |
| NULL |
+------+

select TOP 4 c1 from testtable;

+----+
| C1 |
|----|
|  1 |
|  2 |
|  3 |
| 20 |
+----+

Copy