ORDER BY in Oracle

Order By clause in Oracle arranges or sort the records in the result set in ascending or descending manner.

It is an optional clause.

Sorting of table data helps in the clear understanding of the data and to observe redundant values easily.

ORDER BY Clause

Syntax

ORDER BY {column_name | column_position | expression}
         [ASC | DESC]
         [NULLS FIRST | NULLS LAST]
Parameters Explanation

column_name

  • Column names according to which result set data is to be sorted

column_position

  • Positions of columns in the select statement according to which result set data is to be sorted

expression

  • Expression according to which result set data is to be sorted such as numeric, string or date expressions

ASC

  • Ascending sort of the result set data of the table

DESC

  • Descending sort of the result set data of the table

NULLS FIRST

  • Result set data sort with null values appearing first before not null values

NULLS LAST

  • Data sort with null records appearing last in the result set

Example

  • Sort table data by columns
  SELECT *
    FROM employees
ORDER BY emp_number, hire_date;

Here, employees data is sort first by employee number and then by hire date.

  • Sort table data by position
  SELECT emp_number, hire_date, emp_name
    FROM employees
ORDER BY 1;

Here, employees data is sort by emp_number column as it is at position number 1.

  • Table data sort by an expression
  SELECT emp_number, emp_name, salary, bonus
    FROM employees
ORDER BY salary + bonus;

Here, employees data is sort by the value that is derived from doing the summation.

of salary and bonus columns.

  • Table data by sort by an alias name or correlation name
  SELECT emp_number as eno, emp_name, hire_date
    FROM employees
ORDER BY eno;

Here, employees data is sort by alias name eno.

  • Sort table data by function
  SELECT emp_number, emp_name, hire_date
    FROM employees
ORDER BY ROUND(bonus);

Here, employees data is sort by rounding bonus percentage function.

  • Sort table data with null values as first or last
  SELECT *
    FROM employees
ORDER BY emp_number ASC NULLS FIRST;

Here, employees data is sort by nulls first and then in ascending order of the emp_number column.

SELECT *
    FROM employees
ORDER BY emp_number ASC NULLS LAST;

Here, employees data is sort by emp_number column data in ascending order followed by the null values.

I hope you like the page and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on the latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.