3.3 Sorting Data with ORDER BY

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

3.3 Sorting Data with ORDER BY

Key Takeaways Explain This Topic AI

The ORDER BY clause is syntactically and logically the final clause executed in a standard SELECT query.

Sort order can be specified using column names, column aliases, expressions, or 1-based positional numeric indexes.

By default in Oracle, ascending sorts (ASC) place NULL values at the end (NULLS LAST), while descending sorts (DESC) place NULL values at the beginning (NULLS FIRST).

Default NULL sorting order can be explicitly overridden using the NULLS FIRST and NULLS LAST keywords.

The ANSI standard Row Limiting clause (OFFSET n ROWS FETCH NEXT m ROWS ONLY | WITH TIES) allows clean result set pagination.

Last updated: August 2026

3.3 Sorting Data with ORDER BY

Quick Answer: The ORDER BY clause controls the presentation order of rows in query results. Without an ORDER BY clause, the order of returned rows is non-deterministic (arbitrary). Rows can be sorted in ascending ( ASC , default) or descending ( DESC ) order by column names, aliases, positional numbers, or expressions. By default, Oracle sorts NULL values last in ASC order and first in DESC order, which can be overridden with NULLS FIRST and NULLS LAST .

ORDER BY Clause Placement and Execution

The ORDER BY clause is always written as the last clause in a SELECT statement: SELECT employee_id, last_name, hire_date, salary

• FROM employees
• WHERE department_id = 80
• ORDER BY hire_date DESC;

Because ORDER BY executes after the SELECT list has been projected, it has unique capabilities compared to other clauses:

It can reference column aliases defined in the SELECT clause.

It can sort by expressions or columns not listed in the SELECT clause (provided DISTINCT or UNION is not used).

Sorting Options: ASC, DESC, and Collation

ASC (Ascending) : Default sort order.

Numeric: Lowest to highest ( 1, 2, 3... ).

Date: Earliest to latest ( 01-JAN-20 before 01-JAN-21 ).

Character: Alphabetical ( A to Z ). In standard binary collation, all uppercase letters ( A-Z ) precede lowercase letters ( a-z ).

DESC (Descending) : Reverses the sort order (highest/latest/Z-A first). -- Ascending order (ASC keyword is optional)

SELECT last_name, salary FROM employees ORDER BY salary ASC;

• - Descending order
• SELECT last_name, salary FROM employees ORDER BY salary DESC;
• Four Ways to Specify Sort Columns

Oracle SQL provides four ways to identify columns or expressions in the ORDER BY clause:

• 1. By Column Name SELECT employee_id, last_name, salary
• FROM employees
• ORDER BY last_name ASC;
• 2. By Column Alias

Unlike the WHERE clause, the ORDER BY clause can reference column aliases because ORDER BY executes after SELECT . SELECT employee_id, last_name, salary * 12 AS annual_sal

• FROM employees
• ORDER BY annual_sal DESC;
• 3. By Positional Index (1-based)

You can specify the numeric position of the column as it appears in the SELECT list (starting at 1). -- Sorts by the 3rd column (salary) descending

• SELECT employee_id, last_name, salary
• FROM employees
• ORDER BY 3 DESC;

Warning

Positional sorting is valid in SQL, but referencing a number greater than the number of columns in the SELECT list produces an ORA-01785: ORDER BY item must be the number of a SELECT-list expression error.

4. By Expressions or Unselected Columns

You can sort by columns that are not included in the SELECT list: -- Valid: hire_date is not in the SELECT list

• SELECT employee_id, last_name, salary
• FROM employees
• ORDER BY hire_date;

Important

The DISTINCT Restriction: If the SELECT statement uses the DISTINCT keyword, the ORDER BY clause CANNOT reference columns or expressions that do not appear in the SELECT list. Doing so triggers ORA-01791: not a SELECTed expression .

Multi-Column Sorting

You can sort by multiple columns by separating them with commas. Oracle sorts by the first (primary) column, and resolves ties using the second (secondary) column, and so on. SELECT department_id, last_name, salary

• FROM employees
• ORDER BY department_id ASC, salary DESC;

In this example:

All rows are grouped and ordered by department_id in ascending order.

Within each department_id , rows are ordered by salary from highest to lowest.

Notice that ASC or DESC applies only to the column immediately preceding it .

Oracle NULL Sorting Behavior

In Oracle SQL, NULL values are treated as the highest possible value for sorting comparisons.

Default Behavior:

ASC sort : NULL values appear LAST ( NULLS LAST ).

• DESC sort : NULL values appear FIRST ( NULLS FIRST ). -- Commission NULLs appear at the very bottom
• SELECT employee_id, last_name, commission_pct
• FROM employees
• ORDER BY commission_pct ASC;

• - Commission NULLs appear at the very top
• SELECT employee_id, last_name, commission_pct
• FROM employees
• ORDER BY commission_pct DESC;
• Overriding with NULLS FIRST and NULLS LAST

You can explicitly control where nulls appear regardless of sort direction using NULLS FIRST or NULLS LAST : -- Ascending sort, but push NULLs to the top

• SELECT employee_id, last_name, commission_pct
• FROM employees
• ORDER BY commission_pct ASC NULLS FIRST;

• - Descending sort, but push NULLs to the bottom
• SELECT employee_id, last_name, commission_pct
• FROM employees
• ORDER BY commission_pct DESC NULLS LAST;

• Sort Direction Default NULL Position Overridden Syntax Overridden NULL Position
• ASC NULLS LAST ORDER BY col ASC NULLS FIRST NULLS FIRST
• DESC NULLS FIRST ORDER BY col DESC NULLS LAST NULLS LAST
• The Row Limiting Clause (OFFSET & FETCH)

Oracle Database supports the ANSI standard Row Limiting clause ( OFFSET ... FETCH ), simplifying pagination without requiring legacy ROWNUM subqueries.

• Syntax Structure [ OFFSET offset_rows { ROW | ROWS } ]
• [ FETCH { FIRST | NEXT } [ num_rows | percent PERCENT ] { ROW | ROWS } { ONLY | WITH TIES } ]
• Key Row Limiting Patterns
• 1. Top-N Query ( FETCH FIRST n ROWS ONLY ) -- Retrieve top 5 highest earners
• SELECT employee_id, last_name, salary
• FROM employees
• ORDER BY salary DESC
• FETCH FIRST 5 ROWS ONLY;
• 2. Handling Ties ( WITH TIES )

When multiple rows share the same sort values as the N-th row, WITH TIES returns all tying rows (requiring an ORDER BY clause): -- If 5th and 6th employees both earn 12,000, both are returned (total 6 rows)

• SELECT employee_id, last_name, salary
• FROM employees
• ORDER BY salary DESC
• FETCH FIRST 5 ROWS WITH TIES;
• 3. Pagination with OFFSET -- Skip the first 10 rows and fetch the next 5 rows (rows 11-15)
• SELECT employee_id, last_name, salary
• FROM employees
• ORDER BY salary DESC
• OFFSET 10 ROWS
• FETCH NEXT 5 ROWS ONLY;
• 4. Fetch by Percentage -- Fetch top 10% of employee records
• SELECT employee_id, last_name, salary
• FROM employees
• ORDER BY salary DESC
• FETCH FIRST 10 PERCENT ROWS ONLY;