SORTING
·
Introduction
·
Sorting
on single columns.
·
Sorting
on multiple columns.
·
Sorting
on a columns with null values.
·
Combining
WHERE and ORDER BY clause.
·
Sorting
on DATE columns.
INTRODUCTION:-
Sorting
refer to arrange record in a specified sequence may be alphabetically or by
values.
This sorting
can be achieved by the ‘ORDER BY’ clause of select statement.
SORTING ON SINGLE COLUMNS:-
SQL> desc
collage;
Name
Null? Type
----------------------------------------------------------------
-------- --------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME
VARCHAR2(10)
MARKS NUMBER(5)
SQL>
select * from collage;
COLLAGE ROLLNO NAME MARKS
--------------------
---------- ---------- ----------
rkdf 1 sanjay 12
rkdf 2 salaj 34
oist 3 rahul 23
ptl 4 sunil 45
oist 5 neelesh 43
rkdf 6 ruchi 21
rkdf 7 sarika 49
rkdf 8 satish 32
8 rows
selected.
Arranging record by collage in ascending
order:-
SQL>
select * from collage order by collage;
COLLAGE ROLLNO NAME MARKS
--------------------
---------- ---------- ----------
oist 5 neelesh 43
oist 3 rahul 23
ptl 4 sunil 45
rkdf 8 satish 32
rkdf 1 sanjay 12
rkdf 6 ruchi 21
rkdf 7 sarika 49
rkdf 2 salaj 34
Ascending
order is implies by default whenever we use “ORDER BY” clause.
SQL> select * from collage order by collage asc;
COLLAGE ROLLNO NAME MARKS
--------------------
---------- ---------- ----------
oist 5 neelesh 43
oist 3 rahul 23
ptl 4 sunil 45
rkdf 8
satish 32
rkdf 1 sanjay 12
rkdf 6
ruchi 21
rkdf 7 sarika 49
rkdf 2 salaj 34
8 rows
selected.
Sorting on multiple column:-
Now we want
to arrange record first by collage in ascending order and then follow by mark
in descending order.
This mean
that student will be show in order of their descending marks within a collage such
that the highest score display first followed by the next highest score.
SQL> select * from collage order by collage
asc,marks desc;
COLLAGE ROLLNO NAME MARKS
--------------------
---------- ---------- ----------
oist 5 neelesh 43
oist 3 rahul 23
ptl 4 sunil 45
rkdf 7 sarika 49
rkdf 2 salaj 34
rkdf 8 satish 32
rkdf 6 ruchi 21
rkdf 1 sanjay 12
8 rows
selected.
Notes:-
“ASC” IMPLIES ACCENDING ORDER.
“DESC” IMPLIES DECENDING ORDER.
“ASC” CAN’T REPLACE D BY “ACCENDING
ORDER”.
“DESC” CANT’T REPLACED BY “DESCENDING ORDER”.
The statement
will arrange the record displaying the lowest scores first followed by the next
lowest and so on irrespective of collage.
SQL> select * from collage order by marks;
COLLAGE ROLLNO NAME MARKS
-------------------- ----------
---------- ----------
rkdf 1 sanjay 12
rkdf 6 ruchi 21
oist 3 rahul 23
rkdf 8 satish 32
rkdf 2 salaj 34
oist 5 neelesh 43
ptl 4 sunil 45
rkdf 7 sarika 49
8 rows
selected.
The statement
will arrange the record displaying the highest score first followed by the next
highest and so on irrespective of collage.
SQL> select * from collage order by marks desc;
COLLAGE ROLLNO NAME MARKS
--------------------
---------- ---------- ----------
rkdf 7 sarika 49
ptl 4 sunil 45
oist 5 neelesh 43
rkdf 2 salaj 34
rkdf 8 satish 32
oist 3 rahul 23
rkdf 6 ruchi 21
rkdf 1 sanjay 12
8 rows
selected.
Sorting on a columns with null
values:-
Null indicate
an unknown values and therefore record with NULL values are listed at the end
while sorting in ascending order.
SQL>
select * from collage order by marks;
COLLAGE ROLLNO NAME
MARKS
--------------------
---------- ---------- ----------
rkdf 1 sanjay 12
rkdf 6 ruchi 21
oist 3 rahul 23
oist 9 31
rkdf 8 satish
32
rkdf 2 salaj 34
oist 5 neelesh 43
ptl 4 sunil 45
rkdf 7 sarika 49
oist 10 abc
oist 11 xyz
11 rows
selected.
Notes:-
Student with
null marks are display at the end.
Display the record with null values
on top:-
We can
display the student with null values on top using the “ORDER BY NULLS FIRST”
SQL> select * from collage order by marks nulls
first;
COLLAGE ROLLNO NAME MARKS
--------------------
---------- ---------- ----------
oist 10 abc
oist 11 xyz
rkdf 1 sanjay 12
rkdf 6 ruchi 21
oist 3 rahul 23
oist 9 31
rkdf 8 satish 32
rkdf 2 salaj 34
oist 5 neelesh 43
ptl 4 sunil 45
rkdf 7 sarika 49
11 rows
selected.
Notes:-
·
The
default null are show at last. We can also specify the nulls last in “order by”.
·
The
nulls first feature is available from oracle9i onwards only. If you have
earlier version of oracle then using null first will produce error.
Combining where and order by clause:-
SQL is
highly structure language and require the WHERE clause to appears before the
ORDER BY clause.
SQL>
select * from collage where collage ='oist' order by marks desc;
COLLAGE ROLLNO NAME
MARKS
--------------------
---------- ---------- ----------
oist 10 abc
oist 11 xyz
oist 5 neelesh 43
oist 9 31
oist 3 rahul 23
Notes:-
“marks desc”
has displayed the students with NULLS marks on top.
Sorting on date columns:-
Where we
have a columns named hiredate which indicate the date of joining the company.
Now we want
to display the employees in descending order of joining date.
SQL>
select * from emp order by hiredate desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7654 MARTIN SALESMAN
7698 28-SEP-81 1250
1400 30
7844 TURNER SALESMAN
7698 08-SEP-81 1500 0 30
7782 CLARK MANAGER
7839 09-JUN-81 2450 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7521 WARD
SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81
1600 300 30
7369 SMITH
CLERK 7902 17-DEC-80
800 20
14 rows
selected.
Notes:-
Do not use
TO_CHAR() built in function for date column for DATE column in order by clause
because in convert DATE data types to text and the entire sorting will be
wrong.
SQL>
select * from emp order by to_char(hiredate,'dd/mm/yyyy');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER
7839 01-MAY-81 2850 30
7566 JONES MANAGER
7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7900 JAMES
CLERK 7698 03-DEC-81 950 30
7844 TURNER SALESMAN
7698 08-SEP-81 1500 0 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7369 SMITH CLERK 7902 17-DEC-80
800 20
7788 SCOTT ANALYST 7566 19-APR-87
3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7654 MARTIN SALESMAN
7698 28-SEP-81 1250
1400 30
14 rows
selected.
Thank you
No comments:
Post a Comment