SQL SELECT LIMIT

SQL SELECT LIMIT used to select records from one or more tables and limit the number of rows based on the provided value.

Note that SELECT LIMIT is not supported by all the databases. For Microsoft Access or SQL Server uses SQL SELECT TOP to limit the rows in the result set. 

SQL SELECT LIMIT

The syntax for SQL SELECT LIMIT is as follows.

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows [ OFFSET offset_value ];

In this syntax,

  • expressions – The columns or calculations that you wish to retrieve.
  • tables – Name of the tables from where data will be retrieved.
  • WHERE conditions – Optional. The condition must be met to be part of the result-set.
  • ORDER BY expression [ASC|DESC] – Optional. This is used to sort the selected rows in ascending or descending order. ASC is used to sort in ascending order and DESC is used to sort in descending order.
  • LIMIT number_rows – It limits the number of rows in the result set to be returned based on the row_numbers. For example, suppose you have mentioned LIMIT 5, then the query will return 5 rows in the result set.
  • OFFSET offset_value – Optional. The first row returned by LIMIT will be determined by offset_value. For example, if you mentioned LIMIT 5 OFFSET 2, then first 2 will be skipped and shows next 5 records.

SQL SELECT LIMIT Example

We will consider below Employee table for reference and example.

emp_id emp_name emp_gender dept_code emp_location
1510 Avinash Sirsath M 101 Mumbai
1511 Ramjan Ali M 105 Kolkata
1512 Piyanka Saha F 103 Kolkata
1513 Piyam mondal M 101 Kolkata
1514 Jitu Garg M 104 Mumbai
1515 Raman Sahani M 105 Delhi
1516 Lata Malhotra F 104 Mumbai
1517 Prakash Javedkar M 101 Delhi
1518 Nitu Rani F 103 Mumbai
1519 Pallab Das M 103 Kolkata
1520 Pankaj Sharma M 101 Bangalore

The below example will limit the records to 3 rows as we have specified LIMIT 3 in the SQL query.

SELECT * FROM Employee
ORDER BY emp_id
LIMIT 3;
emp_id emp_name emp_gender dept_code emp_location
1510 Avinash Sirsath M 101 Mumbai
1511 Ramjan Ali M 105 Kolkata
1512 Piyanka Saha F 103 Kolkata

The below example uses OFFSET and skipped first two rows as specified in the OFFSET clause.

SELECT * FROM Employee
ORDER BY emp_id
LIMIT 3 OFFSET 2;
emp_id emp_name emp_gender dept_code emp_location
1512 Piyanka Saha F 103 Kolkata
1513 Piyam mondal M 101 Kolkata
1514 Jitu Garg M 104 Mumbai