Oracle

In Oracle, the ORDER BY clause is used in SQL queries to sort the result set based on one or more columns. By default, the sorting is in ascending order, but it can be changed to descending order by specifying the DESC keyword. The ORDER BY clause is typically placed at the end of a SELECT statement and can sort based on numeric, text, or date columns. Sorting helps in organizing data in a meaningful way, especially when generating reports or lists.

Key Information on ORDER BY in Oracle

1. Basic Syntax

   • The ORDER BY clause can be added at the end of the SELECT query.
   • You can specify multiple columns for sorting.

   Example:

   SELECT * FROM books
   ORDER BY title ASC;
   

2. Sorting by Multiple Columns

   • When sorting by multiple columns, Oracle first sorts by the first column and then by the second if there are duplicates in the first column.

   Example:

   SELECT * FROM books
   ORDER BY author_name, publication_year DESC;
   

   • Bullet Points
     • First, author_name will be sorted alphabetically.
     • Within the same author, publication_year will be sorted in descending order.

3. Ascending (ASC) and Descending (DESC) Orders

   • By default, Oracle sorts data in ascending order (ASC), but you can change this to descending (DESC).

   Example:

   SELECT * FROM rentals
   ORDER BY rental_date DESC;
   

4. Using Column Position

   • Instead of specifying column names, you can use the column’s position in the SELECT list for sorting.

   Example:

   SELECT author_name, title FROM books
   ORDER BY 2;  -- Sorts by the second column (title)
   

5. Combining ORDER BY with WHERE Clause

   • You can combine the ORDER BY clause with a WHERE clause to filter and then sort data.

   Example:

   SELECT * FROM membership
   WHERE status = 'active'
   ORDER BY join_date;
   

6. Best Practices

   • Bullet Points
     • Avoid using column positions in ORDER BY, as it can lead to confusion when modifying the SELECT list.
     • Always be explicit by using column names for clarity and maintainability.
     • Use indexes on columns frequently used in ORDER BY to improve query performance.

By using the ORDER BY clause effectively, you can ensure that your result sets are sorted in a way that makes sense for your data and application.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT *
FROM act_order
ORDER BY order_date ASC;

SELECT *
FROM act_order
ORDER BY order_date ASC;

SELECT *
FROM act_order
ORDER BY order_date DESC;

SELECT *
FROM prd_product
ORDER BY product_name ASC;

SELECT *
FROM prd_product
ORDER BY product_name DESC;

SELECT order_detail_id, product_id, quantity, unit_rate, (quantity * unit_rate) AS total_price
FROM act_order_detail
ORDER BY (quantity * unit_rate) ASC;

SELECT order_detail_id, product_id, quantity, unit_rate, (quantity * unit_rate) AS total_price
FROM act_order_detail
ORDER BY (quantity * unit_rate) DESC;

SELECT a.product_name, SUM(b.quantity) AS units_sold
FROM prd_product a
INNER JOIN act_order_detail b ON b.product_id = a.product_id
GROUP BY a.product_name
ORDER BY SUM(b.quantity) DESC;

SELECT a.first_name AS client_name, b.order_number
FROM org_client a
INNER JOIN act_order b ON b.client_id = a.client_id
ORDER BY b.order_date DESC;

SELECT client_id, birth_year, first_name, last_name
FROM org_client
ORDER BY birth_year DESC, first_name ASC, last_name ASC;

Example 1:

Let's understand the process of extracting data from employee table using ORDER BY sql clause. In this case, we aim to sort the data by employee's last name.

Example 1 - Raw data from employee table

Example 1 - Query

SELECT employee_number, first_name, last_name
FROM org_employee
ORDER BY first_name;

Example 1 - Query data mapping

In the depicted illustration, the data highlighted in green represents the selected information that aligns with our query criteria. Additionally, the red box signifies the column requiring alphabetical sorting prior to generating the ultimate output.

Example 1 - Query Output

Example 2:

Let's explore the procedure of retrieving data from the client's table using the SQL ORDER BY clause. In this example, our initial objective is to arrange the data in descending order based on the year of birth. Following that, we proceed to sort the output from step 1 based on the first name in ascending order.

Example 2 - Raw data from client table

Example 2 - Query

SELECT client_id, birth_year, first_name, last_name
FROM org_client
ORDER BY birth_year DESC, first_name ASC;

Example 2 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query. Red box indicates that as step1 we need to sort the data by year of birth in descending order.

Example 2 - Step 2

In the depicted image, the data has been arranged in descending order based on the specified year of birth, and an additional sorting is necessary based on the first name in ascending order.

Example 2 - Query Output