Summary -

In this topic, we described about the Select Limit with detailed example.

SELECT LIMIT is used to limit the rows displaying from the result-set based on the specified limit value in statement. SELECT LIMIT does not support all SQL Databases. SELECT LIMIT works only on MySQL.

Syntax -

SELECT column1, column2, …, columnN
FROM table_name
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_of_rows [OFFSET offset_value]]; 
  • column1, column2, …, columnN – Specifies the columns to be displayed in output.
  • number_of_rows – Specifies how many rows displayed from the first row.
  • offset_value – Specifies how many rows should skip from the first row.

Example

consider employee_details table are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Fetch first three rows using SELECT LIMIT.

Requirement - Fetch all details of first 3 employees from employee_details table. The query was as follows -

SELECT * FROM employee_details LIMIT 3; 

By executing above query, we can get results as shown below -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000

Scenario – Fetch first two rows.

Requirement1– Fetch first two employees who got highest salaries from employee_details table. The query was as follows –

SELECT * FROM employee_details ORDER BY salary DESC LIMIT 2; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000

Requirement2 - Fetch first two employees who got lowest salaries from employee_details table. The query was as follows –

 SELECT * FROM employee_details ORDER BY salary ASC LIMIT 2; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000

Scenario – Fetch p rows, by skipping q rows from total n rows.

Requirement – Fetch 2nd and 3rd rows belongs to department 1000 from employee_details. The query was as follows -

SELECT * FROM employee_details 
WHERE dept_id = 1000 LIMIT 2 OFFSET 1; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000