TOP Command in SQL

This guide covers top command in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

Note: All databases do not support the TOP clause. For example, MySQL supports the LIMIT clause To retrieve a limited number of records, while Oracle uses The ROWNUM command To perform the same operation.

In this article, we will show you in detail how to use TOP statements in SQL with syntax and specific examples to make it easier to visualize and capture functions better.

TOP Command Syntax in SQL

The basic syntax of TOP command with SELECT statement will be as follows:

SELECT TOP so|phantram tencot
FROM ten_bang
WHERE [dieu_kien]

Example of TOP in SQL

Suppose the NHANVIEN Table has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

The following query is an example on the SQL server, it will retrieve the first 3 records from the NHANVIEN Table .

SQL> SELECT TOP 3 * FROM NHANVIEN;

The above example returns the result:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | +----+----------+-----+-----------+----------+

For MySQL server, this is an equivalent example:

SQL> SELECT * FROM NHANVIEN
LIMIT 3;

The results return the same:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | +----+----------+-----+-----------+----------+

If you are using Oracle Server, then this is an equivalent example:

SQL> SELECT * FROM NHANVIEN
WHERE ROWNUM <= 3;

The results return the same:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | +----+----------+-----+-----------+----------+

In the next section, we will learn about the ORDER BY statement, please keep track.

Previous article: LIKE command in SQL

Next lesson: ORDER BY statement in SQL