Lesson 11:SELECT with ORDER BY

SELECT with ORDER BY clause

When we need to list out records in specific order like ascending or descending, we can use this clause

Syntax

 

SELECT * or list of columns FROM <tablename>   ORDER BY { column-Name | ColumnPosition | Expression }

    [ ASC | DESC ]

    [ NULLS FIRST | NULLS LAST ]

 

column-Name –

The name of the column to be sorted out. The column-Name that you specify in the ORDER BY clause does not need to be the SELECT list.

ColumnPosition

ColumnPosition must be greater than 0 and not greater than the number of columns in the result table.

Expression

A sort key expression, such as numeric, string, and datetime expressions.

ASC

Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.

DESC

Specifies that the results should be returned in descending order.

NULLS FIRST

Specifies that NULL values should be returned before non-NULL values.

NULLS LAST

Specifies that NULL values should be returned after non-NULL values.

Examples :

1)      List out records with employee names in alphabetical order

                 SQL> Select * from emp ORDER BY ename;

                                (or)

                  SQL> Select * from emp ORDER BY ename ASC;

2)      List out records with employee names in descending order

 

                 SQL> Select * from emp ORDER BY ename DESC;

 

3)      List out records  in order of salary+commission in descending order

                 SQL> Select * from emp ORDER BY salary + commision DESC;