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.

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.