Wednesday, October 15, 2014

SORTING DATA IN A TABLE



Oracle allows data from a table to be viewed in a sorted order. The rows retrieved from the table will be sorted in either ascending or descending order depending on the condition specified in the SELECT sentence. The syntax for viewing data in a sorted order is as follows:
Syntax :
SELECT * FROM
ORDER BY , <[Sort Order]>;
ORDER BY clause sorts the result set based on the columns specified. The ORDER BY clause can be used in SELECT statements.
Example:
Show details of the Employee according to the First Name.
SELECT * FROM EMP_MSTR ORDER BY FNAME;
Output:
FNAME          MNAME                     LNAME                      DEPT              DESIG            MNGR_NO
------------------------------------------------------------------------------------------------------------------
Chetan             Manilal                        Bhatt                           Sales                Area Manager             101
Mitesh             Hasmukhbhai              Yadav                          Admin             Clerk                           103
Vimal              Jivandas                      Patel                            Purchase          Manager                      102
Vishal              Kantilal                       Shah                            Sales                Salesman                     104

4 rows selected.

For viewing data in descending sorted order the word DESC must be mentioned after the column name and before the semi colon in the order by clause.
In case of there is no mention of the sort order, the Oracle engine sorts in ascending order by default.

Example :
Show the details of the Employee according to the last name in descending order.

SELECT * FROM EMP_MSTR ORDER BY LNAME DESC;

Output:
FNAME          MNAME                     LNAME                      DEPT              DESIG     MNGR_NO
------------------------------------------------------------------------------------------------------------------
Mitesh             Hasmukhbhai              Yadav                          Admin             Clerk                           103
Vishal              Kantilal                       Shah                            Sales                Salesman                     104
Vimal              Jivandas                      Patel                            Purchase          Manager                      102
Chetan             Manilal                        Bhatt                           Sales                Area Manager             101

4 rows selected.

No comments: