Summary -

In this topic, we described about the ORDER BY LIMIT with detailed example.

ORDER BY LIMIT is used to get rows from table in sorting order either in ascending or descending order and to limit rows in result-set. ORDER BY LIMIT is not supported in all databases. ORDER BY LIMIT works only in MySQL.

Syntax -

SELECT column_name1, column_name2, …, column_nameN 
FROM table_name
[WHERE conditions]
[ORDER BY column_name  ASC | DESC]
LIMIT number_of_rows [ OFFSET offset_value]; 
  • column_name1, column_name2, …, column_nameN – represents the column names.
  • 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 –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

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 rows in descending order of single column by limiting values.

Requirement – Fetch first two employees whose dept_id is 1000 in descending order of emp_id. The query was as follows –

SELECT * FROM employee_details WHERE dept_id = 1000 ORDER BY
 emp_id 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
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000

Scenario – Fetch rows in descending order by skipping some rows from table.

Requirement – Fetch 3rd employee from employee_details table in descending order of emp_name whose dept_id is 2000. The query was as follows –

SELECT * FROM employee_details WHERE dept_id = 2000
ORDER BY emp_name DESC LIMIT 1 OFFSET 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
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000

Scenario – Fetch rows in ascending order of single column by limiting values.

Requirement – Fetch first three employees whose dept_id is 1000 in descending order of emp_id. The query was as follows –

SELECT * FROM employee_details WHERE dept_id = 1000 ORDER BY
 emp_id ASC 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
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000