Order by clause  :-

  • It is used to sort data return by the select statement based on one (or) more columns.
  • Order by clause sort data in descending order (or) In ascending order.

Syntax :

                Select <collist>from<tabname> [ where<cond>] Order by <collist>[asc\desc]

                        [Note : default in ascending order]

Sorting the emptable by salary desc.

Example  :-  select * from emp order by sal

Order by sal desc wise

  •     Select * from emp Order by sal desc.

Sorting the emptable by ename wise   :–

  •    Select  * from emp Order by ename (or) {we can give the column name (or) column number} 
  • Select * from emp Order by 6

( The number is based select list not a table)

Example  :    select empno,ename, sal from emp Order by 3

Example  : Select empno,ename,sal*12 anusal From emp Order by anusal.

{Note   : Column alias are can be used in order by clause but not Used in where clause}

Arrange employee records department wise within the department salary wise

  •          Select deptno,ename,sal,job from emp Order by dept no,sal desc

Example-       deptno                 ename                sal                   job

                              10                           A                  500                clerk

                               10                           B                  400              clerk

                                20                           C                  600            manager

                                 20                           B                   300           manager

                                  30                           D                   200          manager  

Display employee records working for 10 on 20th departments and sort the results salary wise descending order.

  •                  Select * from emp Where deptno in (10,20)  Order by sal desc.