TOP command in SQL

In SQL, the TOP statement is used to retrieve N records or X percent records from a table.

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