SQL Order by

Order by Clause

Order by Clause:- Order by clause is used to sort data in ascending or descending order.

Note :- We can sort data on the basis of one or more columns data.Data can be sort in ascending or descending order. If not order mentioned then data will be sort in ascending order

1) Sorting on one column:-
Example:-
a) By column name :-
select * from emp order by deptno;

Note:- Ascending  is the default clause when we not mentioned , it will automatically attached with order by column. means if we do not mention "asc" or "desc" then data will be arranged in ascending order by default.

select * from emp order by deptno desc;
select * from emp order by ename;
select * from emp order by job desc;

b) By column number :-
select * from emp order by 8;
select * from emp order by 2 desc;

2) Sorting on multiple column:-
Example:-
a) By column name :-
select * from emp order by job , sal ;
select * from emp order by job desc , sal ;
select * from emp order by job desc , sal desc ;

b) By column number :-
select * from emp order by 3 , 5 ;
select * from emp order by 3 desc , 8 ;
select * from emp order by 3 desc , 8 desc ;

c) By column number & column name (Mixed):-
select * from emp order by job , 5 ;
select * from emp order by 3 desc , hiredate ;
select * from emp order by 3 desc , hiredate desc ;

3) Sorting on NULL value column:-
Examples:-
a) By column name :-
select * from emp order by comm nulls first;
select * from emp order by comm desc nulls last;

b) By column number :-
select * from emp order by 8 nulls first;
select * from emp order by 2 desc nulls last;

 4) Random Sorting on column data:- 
Examples:-
select * from emp order by decode(deptno,20,1,30,2,10,3) ;