SQL ORDER BY LIMIT

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

---