• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / clause / ORDER BY Clause in Oracle SQL – PLSQL

ORDER BY Clause in Oracle SQL – PLSQL

October 25, 2012 by techhoneyadmin

The ORDER BY clause in Oracle SQL / PLSQL is used in SELECT statement and allows us to sort the records fetched by SELECT statement.

Syntax for the ORDER BY clause in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE conditions
ORDER BY column(s) ASC/DESC;

The ORDER BY clause will sort the result in ascending order (if ASC is mentioned) or in descending order (if DESC is mentioned) after the column_name in ORDER BY clause.
If nothing is mentioned after the column_name in ORDER BY clause, by default ascending order (ASC) is taken into consideration.

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Suppose we want to see records from ‘employee’ table sorted by ‘department’

We can achieve the same as:

SELECT *
FROM employee
ORDER BY department;

The result of the above query will be:

Employee_ID Employee_Name Salary Department Commission
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
105 Emp E 32000 Sales 10
101 Emp A 10000 Sales 10
104 Emp D 30000 Support

Here we can see that the records have been fetched as per ascending order of the ‘department’.


Scenario 2:

Suppose we want to see the list of employees in reverse order of ‘employee_id’
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

We can achieve the same as:

SELECT *
FROM employee
ORDER BY employee_id  DESC;

Once we have run the above code following will be the result:

Employee_ID Employee_Name Salary Department Commission
105 Emp E 32000 Sales 10
104 Emp D 30000 Support
103 Emp C 28000 IT 20
102 Emp B 20000 IT 20
101 Emp A 10000 Sales 10

Here we can see that the records have been fetched as per descending order of ‘employee_id’.


Filed Under: clause Tagged With: how to use order by clause in oracle database query, how to use order by clause in oracle plsql, how to use order by clause in oracle sql, order by clause in oracle plsql, order by clause in oracle sql, ORDERBYPLSQL, ORDERPLSQL, syntax and example of order by clause in oracle database query, syntax and example of order by clause in oracle plsql, syntax and example of order by clause in oracle sql, using order by clause in oracle database query, using order by clause in oracle plsql, using order by clause in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in