What is ORDER BY clause in Oracle?

ORDER BY clause in Oracle

In Oracle SQL, sorting query results is a basic feature that may be accomplished with the ORDER BY clause. By arranging the rows that a SELECT statement returns in a predetermined order, this clause facilitates data inspection and analysis.

Purpose of the Clause

Oracle is instructed by the ORDER BY clause to arrange the data it retrieves according to your chosen standards. In the absence of an ORDER BY clause, the rows that a query returns will show up in a “random” or arbitrary order, which may change based on how Oracle retrieves the data. Oracle promptly rearranges the table in accordance with your ORDER BY specifications.

Basic Syntax

The SELECT statement can include the optional ORDER BY clause, which usually comes at the end. Following ORDER BY, you provide one or more columns (or expressions) and an optional keyword to indicate the sorting order:

ASC (Ascending): The results are sorted from lowest to highest value using the ASC (Ascending) method. In the absence of ASC or DESC, this is the default sorting order.

DESC (Descending): The results are sorted from highest to lowest value using the DESC (Descending) method.

WHERE and ORDER BY are used as needed to filter and sort the results, respectively, while SELECT and FROM are utilised in every Oracle query.

Single Column Sorting

All you have to do is name the column after ORDER BY to sort by that one column.

Example: Sorting by Temperature in Ascending Order (Default)

SELECT City, Temperature
FROM WEATHER
ORDER BY Temperature;

Output:

City        Temperature
----------- -----------
LIMA                 45
MANCHESTER           66
CHICAGO              66
SYDNEY               69
SPARTA               74
PARIS                81
ATHENS               97

Example: Sorting by Customer ID in Descending Order

SELECT cust_id, cust_state_province, cust_credit_limit
FROM customers
WHERE cust_credit_limit = 15000
AND cust_state_province IN ('UT','CT')
ORDER BY cust_state_province, cust_id DESC;

Output:

CUST_ID CUST_STATE_PROVINCE      CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
104381 CT                                                   15000
103171 CT                                                   15000
102579 CT                                                   15000
102238 CT                                                   15000
101798 CT                                                   15000
101515 CT                                                   15000
103007 UT                                                   15000
24830 UT                                                   15000

In this example, cust_state_province is sorted by default in ascending order, and cust_id is sorted in descending order for each state.

Multiple Column Sorting

Listing many columns, separated by commas, after ORDER BY allows you to sort by more than one column. In order to apply the sort order, the result set is first sorted by the first column, and for rows with the same values in the first column, the second column, and so on. Each column can have its own ASC or DESC specification.

Example: Sorting by Page (Descending) then Feature (Ascending) for Section ‘F’

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Section = 'F'
ORDER BY Page DESC, Feature;

Output:

FEATURE         S  PAGE
--------------- - -----
Classified      F     8
Births          F     7
Doctor Is In    F     6
Obituaries      F     6

Placement within a Statement

The ORDER BY clause typically comes at the very end of a SELECT statement, after other clauses like FROM, WHERE, GROUP BY, and HAVING. In hierarchical queries, the clause order is SELECT, FROM, WHERE, START WITH, CONNECT BY, and finally ORDER BY.

Interactions with Other SQL Clauses

1. WHERE Clause: The WHERE clause is used to filter rows prior to their sorting by ORDER BY. In other words, ORDER BY only applies to rows that meet the WHERE criteria.
2. GROUP BY Clause: This clause groups rows prior to sorting them using ORDER BY. Even though GROUP BY groups “like” items together automatically, its main function is not to create a desired sequence; the ORDER BY clause deals specifically with the final sorting order of the grouped results. When combined with GROUP BY, the ORDER BY clause can employ a column that is defined in the GROUP BY clause or a group function (such as COUNT, SUM, or AVG).
3. Column Aliases: The ORDER BY clause can make use of column aliases that are defined in the SELECT clause.
4. Functions: To sort based on a SQL function’s output, the ORDER BY clause can employ the function directly. To rank cities according to the amount of characters in their names, for instance, use ORDER BY LENGTH(City).
5. Set Operators (UNION, INTERSECT, MINUS):  The ORDER BY clause that applies to the entire combined result set must only exist in the final component query when utilising set operators to combine results from multiple queries. It must use aliases or column positions rather than explicit expressions because the combined result set may not include all queries’ original column names.
6. Views: The CREATE VIEW statement can have an ORDER BY clause. The extra sorting work, however, may result in a performance penalty for queries that go against such views. When querying the view, it is usually more efficient to use ORDER BY instead of embedding it in the view definition, unless the application consistently requires a certain sorted order.
7. ROWNUM Pseudocolumn: Utilising ROWNUM and ORDER BY together requires careful consideration of the order of operations. Results may be inconsistent if ORDER BY and ROWNUM are used in the same query because the rows may be rearranged after ROWNUM values are assigned. A subquery with the ORDER BY clause and an outer query with the ROWNUM condition executed after the sorting process is complete are required to accomplish “top-N reporting” (i.e., obtaining the top 10 smallest employee numbers). To make this pagination easier, Oracle 12c and later versions now have the FETCH FIRST N ROWS ONLY clause for limiting rows.

Performance Considerations

Especially in Online Transaction Processing (OLTP) systems where fast initial response times are crucial, ORDER BY clauses might affect query speed.

• Sorting procedures: Until the complete collection of rows has been sorted, sorting procedures do not provide the user any rows back. In the event that your query calls for a SORT ORDER BY operation on an unindexed column, the user will be delayed until all rows have been reviewed.
• Indexes: By eliminating explicit sorting operations and entire database scans, using an index on the sorted column or columns can greatly increase efficiency. It’s best to minimise needless sorting while tweaking, particularly for internet users.
• FIRST_ROWS Hint: The FIRST_ROWS hint can be used to persuade the optimiser to choose execution pathways that minimise set operations, such as sorting, for applications that rank receiving the first rows rapidly as a top priority.

ORDER BY can be compared to carefully arranging a deck of cards. In its absence, the cards are dealt in a random order, resulting in a mess. If you add ORDER BY, it’s equivalent to directing someone to “sort them by suit, and then by rank within each suit.” After then, the cards are arranged in a methodical manner, which facilitates finding certain cards and comprehending the distribution as a whole. In the same way that a well-organised hand is essential to a successful card game, well-organised query results are frequently necessary for efficient data processing.