TOP <n>¶

Syntax¶

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

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.

• When TOP <n> and ORDER BY are at different nesting levels in a query, results can be unpredictable. For details and examples, see the LIMIT / FETCH usage notes.

• 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 an ORDER BY clause.

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 |
+----+