SQL TOP

 

SQL: SELECT TOP Statement

This SQL tutorial explains how to use the SQL SELECT TOP statement with syntax and examples.

Description

The SQL SELECT TOP statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a fixed value or percentage.

TIP: SELECT TOP is Microsoft's proprietary version to limit your results and can be used in databases such as SQL Server and MSAccess.

For other SQL databases, try the SELECT LIMIT statement.

Syntax

The syntax for the SELECT TOP statement in SQL is:

SELECT TOP (top_value) [ PERCENT ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

TOP (top_value)

It will return the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.

PERCENT

Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ORDER BY expression

Optional. It is used in the SELECT TOP statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.

Example - Using TOP keyword

Let's look at a SQL example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(5)
empno, ename, deptno
FROM emp
ORDER BY empno;

This SQL SELECT TOP example would select the first 5 records from the emp table.

Example - Using TOP PERCENT keyword

Let's look at a SQL example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
contact_id, last_name, first_name
FROM contacts
WHERE last_name = 'Anderson'
ORDER BY contact_id;

This SQL SELECT TOP example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the emp table.