In This Article, You Will Know About SQL Top Clause.
SQL Top Clause– Before moving ahead, let’s know a bit about SQL DELETE Statement.
Table of Contents
SQL SELECT TOP
The SQL “SELECT TOP” Clause specifies the number of records to return.
It is more useful to deal with big data; dealing with big data can impact the final result.
Note: Not all database systems support the SELECT TOP clause, and MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.
Server’s Syntax
SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
MySQL Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Oracle 12 Syntax:
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;
Older Oracle Syntax:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Older Oracle Syntax (with ORDER BY):
SELECT *
FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))
WHERE ROWNUM <= number;
Example Database
Below is the example of a database table named “details.”
Id | Name | Class | section_id |
1 | Alex | 5 | 1 |
2 | Rolex | 6 | 2 |
3 | Salex | 7 | 2 |
4 | Lelex | 8 | 3 |
5 | Kelex | 9 | 5 |
SQL TOP Examples
Example: Use “TOP” clause to return the 4 top records of the table.
SELECT TOP 4 * FROM details;
As a result, it returns top 4 records from the table.
SQL LIMIT Examples
Above same answer is in MySQL.
Example: Use “TOP” clause to return the 4 top records of the table.
SELECT * FROM details
LIMIT 4;
As a result, it returns top 4 records from the table.
SQL FETCH FIRST Examples
Above same answer is in Oracle.
Example: Use “TOP” clause to return the 4 top records of the table.
SELECT * FROM details
FETCH FIRST 4 ROWS ONLY;
As a result, it returns top 4 records from the table.
SQL TOP PERCENT Example
We use “TOP PERCENT” clause to return the specified percentage of top records of the data.
Example: Use “TOP PERCENT” clause to return the top 80% records of the table.
SELECT TOP 75 PERCENT * FROM details;
Above same answer is in Oracle.
SELECT * FROM details
FETCH FIRST 75 PERCENT ROWS ONLY;
ADD a WHERE CLAUSE
The SQL “WHERE” clause helps to specify the condition that from where exactly records has to take.
Example: Use “WHERE” clause to specify the condition to select the records.
SELECT TOP 2 * FROM details
WHERE section_id = 2;
Above same answer is in MySQL.
SELECT * FROM details
WHERE section_id = 2
LIMIT 2;
Above same answer is in Oracle.
SELECT * FROM details
WHERE section_id = 2
FETCH FIRST 2 ROWS ONLY;