Order by clause

ORDER  BY CLAUSE

*.The order by clause is used to sort the records into an  order that is either in ascending  or

    in descending. The default order is ascending.

         Number:  1 to 99.

         Dates:  Earliest to latest

          String:  a to z ,

          Nulls  are last.

 *.The order by clause must be the last clause in select statement.

  *.An  expression or alias name or position of the column is used in order by clause.

  *.The order by clause is used in one column or more than one columns.

Syntax:  select * from tablename [where condition] order by {cols or expr or alias or colpos} [asc/desc];

EX:

1.SQL> select sal from emp order by sal desc;

  SAL

----------

      5000

      3000

      3000

      2975

      2850

      2450

      1600

      1500

      1300

      1250

      1250

      1100

       950

      800

14 rows selected.

2 .select ename,job,sal from emp where job=’CLERK’ order by ename;

ENAME      JOB              SAL

---------- --------- ----------

ADAMS      CLERK           1100

JAMES      CLERK            950

MILLER     CLERK           1300

SMITH      CLERK            800

3.  SQL> select ename,job from emp order by job,ename desc;

ENAME      JOB

---------- ---------

SCOTT      ANALYST

FORD       ANALYST

SMITH      CLERK

MILLER     CLERK

JAMES      CLERK

ADAMS      CLERK

JONES      MANAGER

CLARK      MANAGER

BLAKE      MANAGER

KING       PRESIDENT

WARD       SALESMAN

TURNER     SALESMAN

MARTIN     SALESMAN

ALLEN      SALESMAN

14 rows selected.

4.  SQL> select ename,job from emp order by job,ename desc;
ENAME      JOB

---------- ---------

SCOTT      ANALYST

FORD       ANALYST

SMITH      CLERK

MILLER     CLERK

JAMES      CLERK

ADAMS      CLERK

JONES      MANAGER

CLARK      MANAGER

BLAKE      MANAGER

KING       PRESIDENT

WARD       SALESMAN

TURNER     SALESMAN

MARTIN     SALESMAN

ALLEN      SALESMAN

14 rows selected.

5 . SQL> select ename,sal,sal*12 annsal from emp order by annsal desc;

ENAME             SAL     ANNSAL

---------- ---------- ----------

KING             5000      60000

FORD             3000      36000

SCOTT            3000      36000

JONES            2975      35700

BLAKE            2850      34200

CLARK            2450      29400

ALLEN            1600      19200

TURNER           1500      18000

MILLER           1300      15600

WARD             1250      15000

MARTIN           1250      15000

ADAMS            1100      13200

JAMES             950      11400

SMITH             800       9600

14 rows selected.

6. SQL> select empno,ename,sal from emp order by 2 desc;

EMPNO ENAME             SAL

---------- ---------- ----------

      7521 WARD             1250

      7844 TURNER           1500

      7369 SMITH             800

      7788 SCOTT            3000

      7934 MILLER           1300

      7654 MARTIN           1250

      7839 KING             5000

      7566 JONES            2975

      7900 JAMES             950

      7902 FORD             3000

      7782 CLARK            2450

      7698 BLAKE            2850

      7499 ALLEN            1600

      7876 ADAMS            1100

14 rows selected.

6. SQL> select ename,job,sal from emp where deptno in(20,30) order by deptno,job,sal desc;

ENAME      JOB              SAL

---------- --------- ----------

SCOTT      ANALYST         3000

FORD       ANALYST         3000

ADAMS      CLERK           1100

SMITH      CLERK            800

JONES      MANAGER         2975

JAMES      CLERK            950

BLAKE      MANAGER         2850

ALLEN      SALESMAN        1600

TURNER     SALESMAN        1500

WARD       SALESMAN        1250

MARTIN     SALESMAN        1250

11 rows selected.