Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

ORDER BY in DBMS

ORDER BY  in DBMS

 

In this article, we will learn about ORDER BY in DBMS.

  • Sometimes the user may be interested in arranging the data in the table in some increasing or decreasing order of values
  • Example: If you want to display the details of all students based on descending order of their attendance or marks etc
ORDER BY in DBMS

ORDER BY in DBMS

The order by clause is used to arrange the fetched data from the database table in ascending or descending order of data values based on one or more columns

Syntax

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

ASC: Displays data based on increasing order of values in the column
DESC: Displays data based on decreasing order of values in the column
Consider the following sample EMP table

EMPNOENAMEJOBMGRHIREDATESALDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020

Sorting according to a single column

Here we use only a single column for data arrangement

Display the names, salaries of all employees based on decreasing order of their salaries

select ename,sal from emp
ORDER BY sal DESC;

O/P

7 ROWS SELECTED
ENAMESAL
KING5000
FORD3000
SCOTT3000
JONES2975
BLAKE2850
CLARK2450
SMITH800

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
DEPTNOENAME
10KING
10CLARK
20JONES
20SMITH
20SCOTT
20FORD
30BLAKE

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
ENAMEJOBSALHIREDATE
SCOTTANALYST300019-APR-87
FORDANALYST300003-DEC-81
KINGPRESIDENT500017-NOV-81
CLARKMANAGER245009-JUN-81
BLAKEMANAGER285001-MAY-81
JONESMANAGER297502-APR-81
SMITHCLERK80017-DEC-80

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
ENAMESALDEPTNO
KING500010
CLARK245010
FORD300020
SCOTT300020
JONES297520
SMITH80020
BLAKE285030

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
ENAMEJOBSAL
JONESMANAGER2975
BLAKEMANAGER2850
CLARKMANAGER2450

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
ENAMESALJOB
KING5000PRESIDENT
FORD3000ANALYST
SCOTT3000ANALYST
JONES2975MANAGER
BLAKE2850MANAGER
CLARK2450MANAGER
SMITH800CLERK

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
ENAMEANNUALJOBHIREDATE
KING60000PRESIDENT17-NOV-81
FORD36000ANALYST03-DEC-81
SCOTT36000ANALYST19-APR-87
JONES35700MANAGER02-APR-81
BLAKE34200MANAGER01-MAY-81
CLARK29400MANAGER09-JUN-81
SMITH9600CLERK17-DEC-80

Here we have performed operations on the existing sal column and the rows are  sorted based on this newly formed display purpose column.