Sorting Query Results with ORDER BY

When you retrieve data from an Oracle 19c database using a SELECT statement, the order of the returned rows is not guaranteed unless you explicitly specify it. The ORDER BY clause is fundamental for arranging your query results in a meaningful sequence, making your data more readable and easier to analyze. This clause is always the last clause in a SELECT statement. Master this ORDER BY clause to sort Oracle SQL query results effectively.

Ascending (ASC) and Descending (DESC) Order

The ORDER BY clause allows you to sort your data in either ascending (ASC) or descending (DESC) order. ASC is the default sort order if you don't specify anything, meaning values will be ordered from smallest to largest (for numbers), earliest to latest (for dates), or A to Z (for characters). DESC orders values from largest to smallest, latest to earliest, or Z to A. Understanding Oracle 19c sort order is crucial for precise data retrieval.

Example 1: Oracle 19c SQL SELECT with Basic ASCENDING Sort

SELECT
    employee_id, -- Unique identifier for the employee
    first_name,  -- Employee's first name
    last_name,   -- Employee's last name
    salary       -- Employee's annual salary
FROM
    employees    -- Selecting from the 'employees' table
ORDER BY
    last_name ASC; -- Sorts the results by last name in ascending order (A-Z)

Explanation This SQL query retrieves the employee_id, first_name, last_name, and salary from the employees table. The ORDER BY last_name ASC clause ensures that the result set is sorted alphabetically by the last_name column from A to Z. This is a common requirement for ordering data in Oracle database.

Example 2: Oracle 19c SQL SELECT with Basic DESCENDING Sort

SELECT
    product_id,    -- Unique identifier for the product
    product_name,  -- Name of the product
    list_price     -- Price of the product
FROM
    products       -- Selecting from the 'products' table
ORDER BY
    list_price DESC; -- Sorts the results by list price in descending order (highest to lowest)

Explanation This query fetches the product_id, product_name, and list_price from the products table. The ORDER BY list_price DESC clause arranges the products from the most expensive to the least expensive. This demonstrates how to sort numbers in reverse order in Oracle SQL.

Example 3: Oracle 19c SQL SELECT with Date Column Sorting (Newest First)

SELECT
    order_id,       -- Unique identifier for the order
    customer_id,    -- ID of the customer who placed the order
    order_date,     -- Date when the order was placed
    total_amount    -- Total amount of the order
FROM
    orders          -- Selecting from the 'orders' table
ORDER BY
    order_date DESC; -- Sorts orders by date, showing the newest orders first

Explanation Here, we are retrieving order details from the orders table. The ORDER BY order_date DESC clause sorts the results so that the most recent orders appear at the top, which is very useful for reviewing current activity. This is an essential technique for sorting dates in Oracle SQL from newest to oldest.

Example 4: Oracle 19c SQL SELECT with Character Column Sorting (Z-A)

SELECT
    country_id,  -- Unique identifier for the country
    country_name -- Name of the country
FROM
    countries    -- Selecting from the 'countries' table
ORDER BY
    country_name DESC; -- Sorts countries by name in descending order (Z-A)

Explanation This query retrieves country_id and country_name from the countries table. The ORDER BY country_name DESC clause arranges the country names in reverse alphabetical order, starting with 'Z'. This showcases how to sort text descending in Oracle 19c.

Example 5: Oracle 19c SQL SELECT with NULLs Last (for DESC)

SELECT
    employee_id, -- Unique identifier for the employee
    first_name,  -- Employee's first name
    commission_pct -- Commission percentage for the employee (can be NULL)
FROM
    employees    -- Selecting from the 'employees' table
ORDER BY
    commission_pct DESC NULLS LAST; -- Sorts by commission_pct descending, placing NULL values at the end

Explanation This example demonstrates a specific behavior with NULL values. By default, for DESC sorts, NULL values are placed first. However, NULLS LAST explicitly tells Oracle to put any employees with a NULL commission_pct at the end of the sorted list, even when sorting in descending order. This is key for handling NULLs in Oracle ORDER BY.

Sorting by Multiple Columns

You can specify multiple columns in your ORDER BY clause. When you do this, Oracle sorts the data based on the first column in the list. If there are rows with identical values in the first sorting column, Oracle then uses the second column to sort those "tied" rows, and so on. This provides a powerful way to achieve fine-grained control over your result set's order. This technique is often referred to as multi-column sorting in Oracle SQL.

Example 1: Oracle 19c SQL SELECT Sorting by Two Columns (Ascending)

SELECT
    department_id, -- Unique identifier for the department
    last_name,     -- Employee's last name
    first_name     -- Employee's first name
FROM
    employees      -- Selecting from the 'employees' table
ORDER BY
    department_id ASC, -- First sort by department ID in ascending order
    last_name ASC;     -- Then, for employees within the same department, sort by last name ascending

Explanation This query fetches department and employee names. It first sorts all employees by their department_id in ascending order. If multiple employees belong to the same department, those employees are then sorted by their last_name in ascending order. This illustrates how to sort by multiple columns in Oracle.

Example 2: Oracle 19c SQL SELECT Sorting by Two Columns (Mixed Order)

SELECT
    job_id,        -- Job title identifier
    salary,        -- Employee's annual salary
    first_name     -- Employee's first name
FROM
    employees      -- Selecting from the 'employees' table
ORDER BY
    job_id ASC,    -- Sort by job ID ascending
    salary DESC;   -- Then, for employees with the same job ID, sort by salary descending

Explanation This example demonstrates a mixed sort order. Employees are first grouped and sorted by job_id in ascending order. Within each job_id group, employees are then sorted by salary in descending order, showing the highest-paid employees for that specific job first. This is a practical use of complex sorting in Oracle 19c.

Example 3: Oracle 19c SQL SELECT Sorting by Three Columns

SELECT
    location_id,   -- Unique identifier for the location
    department_name, -- Name of the department
    manager_id     -- Manager's employee ID
FROM
    departments    -- Selecting from the 'departments' table
ORDER BY
    location_id ASC,    -- First, sort by location ID ascending
    department_name ASC, -- Second, for same location, sort by department name ascending
    manager_id ASC;     -- Third, for same location and department, sort by manager ID ascending

Explanation This query sorts departments based on three criteria. It starts by location_id, then department_name, and finally manager_id. This allows for a very specific and hierarchical ordering of your department data. This highlights hierarchical sorting in Oracle SQL.

Example 4: Oracle 19c SQL SELECT Sorting by Multiple Columns with Expressions

SELECT
    customer_id,        -- Unique identifier for the customer
    order_date,         -- Date when the order was placed
    total_amount,       -- Total amount of the order
    (SYSDATE - order_date) AS days_since_order -- Calculated days since the order was placed
FROM
    orders              -- Selecting from the 'orders' table
ORDER BY
    customer_id ASC,    -- Sort by customer ID
    (SYSDATE - order_date) DESC; -- Then sort by the calculated days since order, newest first

Explanation Here, we are sorting by a combination of a column and a calculated expression. Orders are first sorted by customer_id. Then, for each customer, their orders are sorted by days_since_order in descending order, meaning the most recent orders for that customer appear first. This demonstrates sorting by calculated fields in Oracle.

Example 5: Oracle 19c SQL SELECT Sorting with NULLs and Multiple Columns

SELECT
    manager_id,    -- Manager's employee ID (can be NULL for CEO)
    last_name,     -- Employee's last name
    salary         -- Employee's annual salary
FROM
    employees      -- Selecting from the 'employees' table
ORDER BY
    manager_id ASC NULLS FIRST, -- Sorts by manager ID, putting NULLs (CEO) first
    salary DESC;                -- Then, for employees under the same manager, sort by salary descending

Explanation This example showcases how NULLS FIRST/LAST interacts with multiple columns. Employees are primarily sorted by their manager_id, with those having a NULL manager_id (typically the CEO) appearing first. Within each manager's group, employees are then sorted by salary in descending order. This is crucial for ordering NULL values in Oracle multi-column sorts.

Sorting by Column Position

Instead of using column names, you can refer to columns in the ORDER BY clause by their numeric position in the SELECT list. For instance, ORDER BY 1 refers to the first column in your SELECT statement, ORDER BY 2 to the second, and so on. While this can make queries shorter, it's generally considered less readable and more prone to errors if the SELECT list changes. However, it's a valid and occasionally useful feature for sorting by column number in Oracle.

Example 1: Oracle 19c SQL SELECT Sorting by First Column Position

SELECT
    last_name,   -- First column in the SELECT list
    first_name,  -- Second column
    email        -- Third column
FROM
    employees    -- Selecting from the 'employees' table
ORDER BY
    1 ASC;       -- Sorts by the first column in the SELECT list, which is 'last_name'

Explanation This query sorts the results based on the first column listed in the SELECT statement, which is last_name. ORDER BY 1 is equivalent to ORDER BY last_name. This is a straightforward example of ordering by column position in Oracle SQL.

Example 2: Oracle 19c SQL SELECT Sorting by Second Column Position (Descending)

SELECT
    product_name,  -- First column
    list_price,    -- Second column in the SELECT list
    category_id    -- Third column
FROM
    products       -- Selecting from the 'products' table
ORDER BY
    2 DESC;        -- Sorts by the second column in the SELECT list, which is 'list_price', in descending order

Explanation Here, the ORDER BY 2 DESC clause sorts the products table based on the list_price column (the second column in the SELECT list) from highest to lowest. This demonstrates reverse sorting by column number in Oracle.

Example 3: Oracle 19c SQL SELECT Sorting by Multiple Column Positions

SELECT
    department_id, -- First column
    job_id,        -- Second column
    salary         -- Third column
FROM
    employees      -- Selecting from the 'employees' table
ORDER BY
    1 ASC,         -- Sorts by the first column ('department_id') ascending
    2 ASC;         -- Then, for same department, sorts by the second column ('job_id') ascending

Explanation This query sorts employees first by their department_id (the first column) and then by their job_id (the second column) if the department_id is the same. This showcases multi-column sorting by position in Oracle 19c.

Example 4: Oracle 19c SQL SELECT Sorting by a Calculated Column Position

SELECT
    order_id,         -- First column
    order_date,       -- Second column
    (SYSDATE - order_date) AS days_old -- Third column, a calculated expression
FROM
    orders            -- Selecting from the 'orders' table
ORDER BY
    3 DESC;           -- Sorts by the third column in the SELECT list, which is the calculated 'days_old'

Explanation In this example, we calculate days_old as a new column. The ORDER BY 3 DESC clause then sorts the results based on this calculated days_old column, placing the oldest orders first. This demonstrates that you can sort by the position of an alias in Oracle SQL.

Example 5: Oracle 19c SQL SELECT Combining Column Names and Positions (Not Recommended for Readability)

SELECT
    first_name,  -- First column
    last_name,   -- Second column
    email,       -- Third column
    phone_number -- Fourth column
FROM
    employees    -- Selecting from the 'employees' table
ORDER BY
    last_name ASC, -- Sorts by last name (column name)
    3 DESC;        -- Then, for same last name, sorts by the third column ('email') descending

Explanation While technically possible, mixing column names and column positions in the ORDER BY clause (as shown by last_name ASC, 3 DESC) can significantly reduce the readability and maintainability of your SQL code. It's generally advised to stick to either all column names or all column positions for clarity. This highlights a less common but valid mixed ORDER BY strategy in Oracle.