In this SQL tutorial for beginners, you will learn about SQL SELECT LIMIT, TOP, FETCH FIRST with the help of examples.
The LIMIT
keyword in SQL allows you to specify the number of records to return in a query. We can use the LIMIT
keyword with MySQL, PostgreSQL, and SQLite.
SELECT first_name, age
FROM Customers
LIMIT 2;
Here, the SQL command selects the first 2 rows from the table.
The OFFSET
keyword is used with LIMIT
to specify the starting rows from where to select the data. For example,
-- LIMIT 2 selects two results
-- OFFSET 3 excludes the first three results
SELECT first_name, last_name
FROM Customers
LIMIT 2 OFFSET 3;
Here, the SQL command selects 2 rows starting from the fourth row. OFFSET 3
means the first 3 rows are excluded.
Example: SQL LIMIT Clause with OFFSET
Note: The LIMIT
clause is not supported in all Database Management Systems (DBMS). Different DBMS use different keywords to select a fixed number of rows. For example,
Keyword | Database System |
---|---|
TOP | SQL Server, MS Access |
LIMIT | MySQL, PostgreSQL, SQLite |
FETCH FIRST | Oracle |
The TOP
keyword is used in place of LIMIT
with the following database systems:
SELECT TOP 2 first_name, last_name
FROM Customers;
Here, the SQL command selects first_name and last_name of the first 2 rows.
We can also use *
with TOP
to select all columns.
SELECT TOP 2 *
FROM Customers;
Here, the SQL command selects the first 2 rows from the table.
Example: SQL TOP Clause
The FETCH FIRST n ROWS ONLY
clause is used with the Oracle database system.
Let's look at an example.
SELECT *
FROM Customers
FETCH FIRST 2 ROWS ONLY;
Here, the SQL command selects the first 2 rows from the table.
#sql