ORDER BY in DBMS

ORDER BY in DBMS

On this page we will discuss about 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.
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

  • 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

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
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20

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
ENAME SAL
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
SMITH 800
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
DEPTNO ENAME
10 KING
10 CLARK
20 JONES
20 SMITH
20 SCOTT
20 FORD
30 BLAKE
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
ENAME JOB SAL HIREDATE
SCOTT ANALYST 3000 19-APR-87
FORD ANALYST 3000 03-DEC-81
KING PRESIDENT 5000 17-NOV-81
CLARK MANAGER 2450 09-JUN-81
BLAKE MANAGER 2850 01-MAY-81
JONES MANAGER 2975 02-APR-81
SMITH CLERK 800 17-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
ENAME SAL DEPTNO
KING 5000 10
CLARK 2450 10
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
SMITH 800 20
BLAKE 2850 30
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
ENAME JOB SAL
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
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
ENAME SAL JOB
KING 5000 PRESIDENT
FORD 3000 ANALYST
SCOTT 3000 ANALYST
JONES 2975 MANAGER
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
SMITH 800 CLERK
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
ENAME ANNUAL JOB HIREDATE
KING 60000 PRESIDENT 17-NOV-81
FORD 36000 ANALYST 03-DEC-81
SCOTT 36000 ANALYST 19-APR-87
JONES 35700 MANAGER 02-APR-81
BLAKE 34200 MANAGER 01-MAY-81
CLARK 29400 MANAGER 09-JUN-81
SMITH 9600 CLERK 17-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.

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription