SQL ORDER BY Clause overview and examples

2020-05-06 Rahul Swami Download Code

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.

  1. asc
  2. 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:

       SAL
----------
       800
       950
      1100
      1250
      1250
      1300
      1500
      1600
      2450
      2850
      2975
      3000
      3000
      5000

 

Example 2:

SQL> Select sal from emp ORDER BY sal SESC;

Output:

       SAL
----------
      5000
      3000
      3000
      2975
      2850
      2450
      1600
      1500
      1300
      1250
      1250
      1100
       950
       800

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

 

 


About author

Card image cap
Rahul Swami

Experienced in providing key insights with advanced statistical methods, data modeling, and graphical display, utilizing the software that best suits your needs, with advanced skill in R.!

-R Developer

0 Comments


Scroll to Top