ORDER BY in DBMS

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

select ename,sal from emp
ORDER BY sal DESC;

The row which contains the highest salary is displayed first, the row with the second highest salary is displayed next and so on

Display the department number and employee name as per increasing order of department numbers

 select deptno,ename from emp
ORDER BY deptno;

O/P

7 rows selected

As we have 3 departments in the employee table, first the rows of 10th department followed by 20 and 30 departments are displayed i.e increasing order of department numbers

Note:

ASC is optional and the Default value for order by, If you don’t specify ASC or DSC by default data is arranged in ascending order, but for descending arrangement of data you must specify DESC in order by explicitly

Display all employees for working in a company based on seniority level

select ename,job,sal,hiredate
from emp
order by hiredate desc;

7 rows selected

Employees who are having an older hire date is displayed first followed by the second older hire date and so on.

Sorting according to more than one column

You can  specify more than one column in the order by clause this is done when you further need sorting internally based on a certain column

Display the details of employees based on increasing order of Departments and in each department salary should be further arranged in highest to lowest order

select ename,sal,deptno from emp
ORDER BY deptno,sal DESC;

O/P

7 rows selected

Before displaying the rows of30th department, rows or sorted based on the salaries of30 department in decreasing order i.e that is first highest salary in the 30th department and the second-highest salary in the 30th department followed by the 20th department the first highest salary followed by 20th department second highest salary and so on.

Using where clause in ORDER BY

• You can also specify some conditions and filter the data by using where class and then sort the data
• Here first where clause  is to be followed by order by class.

Display the names, sal, jobs of employees who are working as a manager in highest to lowest order

select ename,job,sal from emp
where job='MANAGER'
order by sal desc ;

O/P

3 rows selected

Here only records of managers are displayed as per decreasing order of their salaries.

Specifying column numbers in order by

Instead of column names, you can also use the position number of columns are specified in the select statement

select ename, sal, job
from emp
order by 2 DESC;

O/P

7 rows selected

The second column in the select statement is ‘sal’ hence in the above query sorting of rows is done as per increasing order of salaries.

Sorting data based on expressions

User can specify basic arithmetic expressions and  sort the rows based on this column generated by the expression.

Display the names, job, annual salary of all employees based on decreasing order of their annual salary

select ename ,sal*12 annsal ,job,hiredate
from emp
order by annsal desc;

O/P

7 ROWS SELECTED