Summary -

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

ORDER BY ASC statement is used to sort the data from table in result-set in ascending order.

ORDER BY ASC is used in SELECT statement.

Syntax -

SELECT column1, column2, …, columnN
FROM table_name
[ WHERE condition ]
ORDER BY column_name ASC; 
  • column1, column2, …, columnN - Specifies the column names from table.
  • table_name – Specifies the name of the table.
  • column_name - Specifies the column used to perform ORDER BY ASC operation.

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 ascending order using column.

Requirement – Fetch emp_name, designation details of all employees from employee table in ascending order of designation. The query was as follows –

 SELECT emp_name, designation FROM employee_details
 ORDER BY designation ASC; 

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

emp_name designation
Employee5 Analyst
Employee6 Analyst
Employee7 Clerk
Employee1 Director
Employee2 Director
Employee3 Manager
Employee4 Manager
Employee8 Salesman
Employee9 Salesman

Scenario – Fetch rows in ascending order using multiple columns.

Requirement – Fetch emp_name, designation, dept_id details of all employees from employee table in ascending order of designation and dept_id. The query was as follows –

SELECT emp_name, designation, dept_id FROM employee_details 
 ORDER BY designation, dept_id ASC; 

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

emp_name designation dept_id
Employee5 Analyst 1000
Employee6 Analyst 1000
Employee7 Clerk 1000
Employee1 Director 1000
Employee2 Director 2000
Employee3 Manager 1000
Employee4 Manager 2000
Employee8 Salesman 2000
Employee9 Salesman 2000

Scenario – Fetch rows in ascending order of a column and descending order of another column.

Requirement – Fetch emp_name, designation, salary, dept_id details of all employees from employee_details table in ascending order of designation and descending order of salary. The query was as follows –

SELECT emp_name, designation, salary, dept_id FROM employee_details
ORDER BY designation ASC, salary DESC; 

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

emp_name designation salary dept_id
Employee5 Analyst 20000.00 1000
Employee6 Analyst 18000.00 1000
Employee7 Clerk 15000.00 1000
Employee1 Director 45000.00 1000
Employee2 Director 40000.00 2000
Employee3 Manager 27000.00 1000
Employee4 Manager 25000.00 2000
Employee8 Salesman 14000.00 2000
Employee9 Salesman 13000.00 2000

Scenario – Fetch rows in ascending order of a column by relative position.

Requirement – Fetch emp_name, salary, manager_id details of all employees from employee_details table in ascending order of relative position of salary column in result-set. The query was as follows –

  SELECT emp_name, salary, manager_id FROM employee_details
 ORDER BY 2 ASC; 

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

emp_name salary manager_id
Employee9 13000.00 Employee4
Employee8 14000.00 Employee4
Employee7 15000.00 Employee3
Employee6 18000.00 Employee3
Employee5 20000.00 Employee3
Employee4 25000.00 Employee2
Employee3 27000.00 Employee1
Employee2 40000.00
Employee1 45000.00