EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial ORDER BY in Oracle
 

ORDER BY in Oracle

Priya Pedamkar
Article byPriya Pedamkar

Updated March 24, 2023

order by in oracle

 

 

Introduction to ORDER BY in Oracle

‘ORDER BY’ in Oracle is a keyword or clause used to sort the data being queried in ascending or descending orders, where ASC is added at the end of the ORDER BY clause for arranging in Ascending order and DESC for Descending order. If the order is not mentioned, that is an ORDER BY clause without ASC or DESC, the system returns the results in ascending order by keeping it as the default sort order. This conditional clause can be applied on a SELECT statement after the main query, with or without a WHERE condition.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

SELECT columns/expressions FROM tables
[where conditions] ORDER BY column/expression [DESC/ASC];

Parameters

Below are some of the important parameters of order by in oracle:

  • columns/expression: The column names or any other calculations based on expressions(aggregate functions) we want to retrieve.
  • tables: Name of the tables from where we want to retrieve the details.
  • [where conditions]: It is an optional clause. If it is provided then the query will retrieve only those records which satisfy the condition mentioned in the WHERE clause.
  • DESC: To order the result set in descending order.
  • ASC: To order the result set in ascending order.

One important point to note that in case the modifier (ASC/DESC) is not mentioned than the default modifier will be ASC (ascending order).

How does ORDER BY Work in Oracle?

In the database, the data is not stored in order while inserting in the table so when we extract records from the database and we want the records to be displayed in any order numerically or alphabetically then we will have to specifically mention it to the oracle database. So, in order to do that, we will have to use the ORDER BY clause. The ORDER BY clause is used to rearrange the extracted data into a specific order as per the user’s wish. One more point to mention is that we can sort the records based on multiple columns where each column may also have different sort orders. The ORDER BY clause is always the last clause mentioned in the SELECT query.

In the above point, we discussed the working and need for the ORDER BY clause. Now we will look at some examples with screenshots to learn more about the ORDER BY clause in the oracle database.

Examples to Implement ORDER BY in Oracle

To have a more clear understanding of the topic we are going to look at a few examples.

Example #1

This is the example of sorting rows in ascending order by a column. For example in our oracle database, we have a table named employee which has six columns and we want to get the employee id and name of the employee but we also want the result set to be sorted in ascending order based on the name of the employee. In that case, we are going to write the below query using the ORDER BY clause.

Query:

SELECT employee_id, name FROM employee ORDER BY NAME ASC;

To get the desired result we have given the column name as well as the sorting method at the end of the select query. ASC stands for ascending order. Let us now run the above query in SQL developer to see the output.

Output:

order by in Oracle eg1

As we can see from the output the result set is sorted in ascending order based on the name column of the employee table.

Example #2

This is the example of sorting rows in descending order by column. Like the previous example, we are going to use the same table employee which has six columns but this time we want to get the employee id and name of the employee sorted in descending order based on the name of the employee. We are going to write the below query using the ORDER BY clause.

Query:

SELECT employee_id, name FROM employee ORDER BY NAME DESC;

To get the desired result we have given the column name as well as the sorting method at the end of the select query. DESC stands for descending order. Let us now run the above query in SQL developer to see the output.

Output:

order by in Oracle eg2

We can see from the output that the result set is sorted in descending order based on the name.

Example #3

This is the example of sorting rows based on the column position in the table. In this case, we will do the previous operation with a little bit of change. In this example, we will not name the column on whose basis we want to do the sorting instead we will provide the position of the column present in the select list expression and then mention the type of sorting. For now, we will do an ascending order. Let us look at the query.

Query:

SELECT employee_id, name FROM employee ORDER BY 2 ASC;

As you can see the name is written second in the select list expression hence we have given the position as 2. Let us now run the above query in SQL developer.

Output:

Example #3

As you can see the result set is sorted based on name in ascending order.

Example #4

This is the example of sorting based on function or expression. In the previous examples, we saw how to use the ORDER BY clause to sort using names or positions of columns. In this example we will see how we can use the ORDER BY clause with functions like UPPER(), LOWER() functions or even mathematical functions. For this example, we will use the UPPER() function to sort the names case-insensitively because we all know that it converts all letters into uppercase.

Query:

SELECT employee_id, city FROM employee ORDER BY UPPER(city);

As you can see we have put the UPPER() function after the ORDER BY clause instead of any column or expressions. Now let us run this query in oracle SQL developer.

Output:

UPPER() function output

As we can see the output is the case–insensitive as we have used the UPPER() function because it converts all cases into upper case.

Conclusion

In this article, we have learned about what is ORDER BY clause and its Syntax. We also learned about how it works and we went through various examples depicting various scenarios where we can use the ORDER BY clause.

Recommended Articles

This is a guide to ORDER BY in Oracle. Here we discuss syntax, parameters, and working of ORDER BY in Oracle along with examples and code implementation. You may also look at the following articles to learn more-

  1. Inner Join in Oracle
  2. Select in Oracle
  3. Oracle Clauses
  4. Joins in Oracle
  5. Aggregate Functions in PostgreSQL
  6. How to Work PostgreSQL ORDER BY?
  7. Guide to Aggregate Functions in SQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW