SQL ORDER BY Clause

Order by clause is used to sorting data either in ascending order or in descending order. Along with order by clause we are using 2 keywords.

asc
desc

By default order by clause having ascending order.

Syntax:

Select * from tablename ORDER BY columnname [ASC/DESC];

Example 1:

SQL> Select sal from emp ORDER BY sal;

Output:

Example 2:

SQL> Select sal from emp ORDER BY sal SESC;

Output:

Example 3:

SQL> Select ename from emp ORDER BY ename DESC;

Output:

ENAME
----------
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK
BLAKE
ALLEN
ADAMS

Note 1:

in Oracle we can also use column alias name or expressions in order by clause.

Example:

SQL> Select ename,sal,sal*12 annsal from emp ORDER BY annsal DESC; (OR)

SQL> Select ename,sal,sal*12 annsal from emp ORDER BY sal*12 DESC;

Output:

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

Note 2:

in Oracle we can also use number within order by clause here that number represents column position within select list.

Example:

SQL> Select ename,sal from emp ORDER BY 2 DESC;

Output:

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250
MARTIN           1250
ADAMS            1100
JAMES             950
SMITH             800

Note 3:

we can also use null value columns within order by clause in this case null values are treated as highest values. to overcome this problem, if you want to control null values within order by clause then Oracle provided nulls first or nulls last clauses .

Syntax:

SELECT * from tablename 
ORDER BY column_name [asc/desc] [nulls first/nulls last];

Example 1:

SQL> Select ename,comm from emp ORDER BY comm DESC;

Output:

ENAME            COMM
---------- ----------
SMITH
CLARK
FORD
JAMES
ADAMS
JONES
BLAKE
MILLER
SCOTT
KING
MARTIN           1400
WARD              500
ALLEN             300
TURNER              0

Example 2:

SQL> Select ename ,comm from emp ORDER BY comm DESC nulls last;

Output:

ENAME            COMM
---------- ----------
MARTIN           1400
WARD              500
ALLEN             300
TURNER              0
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
BLAKE
JONES
SMITH
CLARK

Note 4:

in Oracle we can also use multiple columns within ORDER BY clause in this case Oracle server always sorting data based on first column within ORDER BY clause when ever first column having duplicate data then that group is sorted in second column.

Example:

SQL> Select deptno,sal from emp ORDER BY deptno ASC, sal DESC;

Output:

    DEPTNO        SAL
---------- ----------
        10       5000
        10       2450
        10       1300
        20       3000
        20       3000
        20       2975
        20       1100
        20        800
        30       2850
        30       1600
        30       1500
        30       1250
        30       1250
        30        950

Syntax:

SELECT column1,column2 ,...
FROM table_name 
WHERE condition 
GROUP BY column_name 
HAVING condition 
ORDER BY column_name [ASC/DESC];

Example:

SQL> SELECT deptno,count(*) from emp
WHERE sal>1000
GROUP BY deptno
HAVING count(*)>3
ORDER BY deptno ASC;

Output:

    DEPTNO   COUNT(*)
---------- ----------
        20          4
        30          5

SQL ORDER BY Clause overview and examples

SQL ORDER BY Clause

Search

Categories