Skip to main content

​
What is Sorting Data?

Sorting Data (also known as ordering or result ordering) is the process of arranging query results in a specific sequence based on one or more columns or expressions. In PL/SQL, sorting primarily occurs within SQL queries embedded in PL/SQL blocks, allowing developers to retrieve data in predictable, meaningful sequences.
The core purpose is to impose logical organization on result sets, solving the problem of unpredictable data retrieval where rows return in arbitrary order unless explicitly sorted.

​
ORDER BY Clause

The ORDER BY clause specifies the sorting criteria for a query’s result set. It’s always the last clause in a SELECT statement and can reference columns, expressions, or ordinal positions. 
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, last_name, salary, hire_date
        FROM employees
        ORDER BY hire_date; -- Sorts by hire date in default ascending order
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.last_name || ' hired on ' || emp_rec.hire_date);
    END LOOP;
END;
/

​
ASC Keyword

ASC specifies ascending order (lowest to highest). It’s the default behavior when no direction is specified. 
DECLARE
    CURSOR salary_cursor IS
        SELECT employee_id, last_name, salary
        FROM employees
        ORDER BY salary ASC; -- Explicit ascending order: lowest salary first
BEGIN
    FOR emp_rec IN salary_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.last_name || ': $' || emp_rec.salary);
    END LOOP;
END;
/

​
DESC Keyword

DESC specifies descending order (highest to lowest), reversing the natural order. 
DECLARE
    CURSOR top_earners IS
        SELECT employee_id, last_name, salary, department_id
        FROM employees
        ORDER BY salary DESC; -- Highest salaries first
BEGIN
    FOR emp_rec IN top_earners LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.last_name || ': $' || emp_rec.salary);
    END LOOP;
END;
/

​
Multiple Column Sorting

Multiple columns can be specified with individual sort directions, creating hierarchical sorting.
Use multiple column sorting to create meaningful data hierarchies in your result sets.
DECLARE
    CURSOR dept_emp_cursor IS
        SELECT department_id, last_name, salary, job_id
        FROM employees
        ORDER BY department_id ASC,  -- Primary sort by department
                 salary DESC;        -- Secondary sort: highest salary first within each department
BEGIN
    FOR emp_rec IN dept_emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Dept ' || emp_rec.department_id || ': ' || 
            emp_rec.last_name || ' - $' || emp_rec.salary
        );
    END LOOP;
END;
/

​
Sorting Null Values

By default, NULL values sort last in ascending order and first in descending order. This can be controlled using NULLS FIRST/LAST.
Use NULLS FIRST or NULLS LAST to explicitly control how NULL values are positioned in sorted results.
DECLARE
    CURSOR commission_cursor IS
        SELECT employee_id, last_name, commission_pct
        FROM employees
        ORDER BY commission_pct NULLS FIRST; -- NULL commission values appear first
BEGIN
    FOR emp_rec IN commission_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.last_name || ': ' || 
            NVL(TO_CHAR(emp_rec.commission_pct), 'No commission')
        );
    END LOOP;
END;
/

​
Positional Sorting

Columns can be referenced by their ordinal position in the SELECT list rather than by name. 
DECLARE
    CURSOR pos_sort_cursor IS
        SELECT last_name, salary, hire_date  -- Position 1: last_name, 2: salary, 3: hire_date
        FROM employees
        ORDER BY 3 DESC, 2 ASC;  -- Sort by hire_date (pos 3) DESC, then salary (pos 2) ASC
BEGIN
    FOR emp_rec IN pos_sort_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.last_name || ' - Hired: ' || emp_rec.hire_date || 
            ', Salary: $' || emp_rec.salary
        );
    END LOOP;
END;
/

​
Expression Sorting

Sorting can be based on expressions, functions, or calculations rather than raw column values. 
DECLARE
    CURSOR expr_sort_cursor IS
        SELECT employee_id, last_name, salary, commission_pct,
               (salary * NVL(commission_pct, 0)) as potential_earnings
        FROM employees
        ORDER BY UPPER(last_name),  -- Case-insensitive sort by name
                 (salary * 12) DESC;  -- Sort by annual salary
BEGIN
    FOR emp_rec IN expr_sort_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(
            emp_rec.last_name || ' - Monthly: $' || emp_rec.salary ||
            ', Potential: $' || emp_rec.potential_earnings
        );
    END LOOP;
END;
/

​
Why is Sorting Data Important?

​
Predictable Data Retrieval

Follows the Principle of Least Astonishment - ensures consistent result ordering across executions.
Sorting makes application behavior reliable and testable by providing consistent result sets.

​
Business Logic Implementation

Allows business rules like “show highest priority items first” to be enforced directly in data access layer rather than application code (Single Responsibility Principle).

​
Presentation Readiness

Prepares data for user interfaces without requiring client-side sorting, improving performance and reducing network overhead (Separation of Concerns).

​
Advanced Nuances

​
Performance Implications with Large Datasets

Complex sorts on large result sets can cause temporary tablespace usage. Use WHERE clauses to limit data before sorting.
Senior developers use WHERE clauses to limit data before sorting and consider indexing strategy for frequently sorted columns.

​
Dynamic Sorting in PL/SQL

Advanced applications often require dynamic ORDER BY clauses using EXECUTE IMMEDIATE: 
DECLARE
    sql_stmt VARCHAR2(1000);
    sort_column VARCHAR2(30) := 'salary';
    sort_direction VARCHAR2(4) := 'DESC';
BEGIN
    sql_stmt := 'SELECT last_name, salary FROM employees ORDER BY ' || 
                sort_column || ' ' || sort_direction;
    -- Execute dynamic SQL
END;
/

​
Window Function Integration

Sorting interacts with analytic functions where ORDER BY in OVER() clause defines window frame boundaries: 
SELECT last_name, salary,
       AVG(salary) OVER (ORDER BY hire_date) running_avg
FROM employees;

​
How This Fits the Roadmap

Within the “Basic SQL Querying” section, Sorting Data serves as a bridge between fundamental data retrieval (SELECT, FROM, WHERE) and more advanced data manipulation concepts.

​
Prerequisites For:

  • Pagination Techniques - Using ROWNUM with ORDER BY for limit/offset patterns
  • Analytic Functions - Window functions that rely on sorted data for calculations
  • Top-N Queries - Retrieving ranked results using subqueries and sorting
  • Reporting Queries - Generating hierarchical and grouped reports

​
Unlocks:

  • Advanced reporting capabilities
  • Performance optimization techniques
  • Data analytics features
  • Sophisticated PL/SQL applications

Build docs developers (and LLMs) love

Get started for freeTalk to us