EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

ORDER BY in Oracle

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » ORDER BY in Oracle

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.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,568 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects

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

Oracle Training (14 Courses, 8+ Projects)

14 Online Courses

8 Hands-on Projects

120+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Independence Day Offer - Oracle Training (14 Courses, 8+ Projects) Learn More