PL/SQL ORDER BY Clause

In PL/SQL, the ORDER BY clause is a vital tool that allows for the sorting of query results by one or more columns, either in ascending or descending order. In this article, We will learn about ORDER BY clause in PL/SQL, its syntax, functionality, and practical usage through examples.

Understanding ORDER BY in PL/SQL

• The ORDER BY clause in PL/SQL is used to sort the result set of a SQL query by one or more columns, either in ascending (ASC) or descending (DESC) order.
  
• Sorting the results helps to organize and present the data in a meaningful way, such as displaying records in alphabetical order, sorting by numeric values or ordering dates.

Syntax:

SELECT column1, column2, ...
FROM table_name 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Key Terms:

• table_name: The name of the table from which to retrieve data.
  
• column1, column2, ...: The columns by which the result set will be sorted.
  
• ASC: It is used for Sorting the result set in ascending order (default behavior).
  
• DESC: It is used for Sorting the result set in descending or decreasing order.
  

Examples of PL/SQL ORDER BY Clause

To illustrate how the ORDER BY clause functions in PL/SQL, let's start by creating a table and inserting some sample data. We'll create a table named Employees with fields such as emp_id, name and salary. By setting up this example once, we can focus on understanding the effects of the ORDER BY clause without redundant table creation.

Creating the 'employee' Table

CREATE TABLE employee (
  id INT,
  name VARCHAR2(50),
  salary NUMBER,
);

Inserting sample data

INSERT INTO employee (id, name, salary, join_date)
VALUES 
  (1,  'Aryan',  60000),
  (2,  'Sam',  75000),
  (3,  'Ritika',  65000),
  (4,  'Bobby',  70000);

Output:

Example 1: Basic Sorting by 'salary' in Ascending Order

In this example, we will sort the list of employees based on their salary. By default, the ORDER BY clause sorts data in ascending order, meaning the lowest salary will appear first, and the highest salary will appear last. This is useful when you want to see the employees arranged from the least paid to the most paid.

Query:

SELECT id, name, salary
FROM employee
ORDER BY salary;

Output:

Explanation: Here, we use the ORDER BY clause followed by the salary column. Since we didn't specify any sorting direction, the query will automatically sort the results in ascending order. Here the employee with the smallest salary will appear at the top of the list, and the one with the highest salary will be at the bottom.

Example 2: Basic Sorting by 'salary' in Descending Order

In this query, we sort the list of employees by their salary in descending order using the ORDER BY clause with the DESC keyword. This means that the highest salary will appear first, and the lowest salary will appear last. By adding DESC we reverse the default ascending order.

This will make it easy to see which employees are earning the most at the top of the list and those earning the least at the bottom. This is useful when you want to quickly identify the highest-paid employees in the company.

Query:

SELECT * FROM employee
ORDER BY salary DESC;

Output:

Explanation: The output shows employees listed from highest to lowest salary. Sam, with a salary of $75,000, is at the top, followed by Bobby with $70,000. Next is Ritika with $65,000, and finally, Aryan with $60,000. This arrangement makes it clear who the highest-paid and lowest-paid employees are.

Example 3: Multilevel Sorting

In Multilevel Sorting the query sorts employees first by salary in descending order, such that the highest salaries come first and The employees with the same salary are sorted in ascending order by name. This ensures that within each salary group, names are listed alphabetically.

Query:

SELECT * FROM employee
ORDER BY salary DESC, name ASC

Output:

Explanation: The output shows employees are sorted first by salary, with highest salaries at the top. If multiple employees have the same salary, they are then sorted by name in alphabetical order. This ensures a clear ranking is done by salary and among those with identical salaries, the names are sorted alphabetically

Example 4: Sorting by relative position

We can also use the ORDER BY clause to sort the data by relative position in the result set, where the first field in the result is set to 1. The next field is 2, and so on. In the below example, the result set contains four columns. So, the relative position will be 1 for id, 2 for name, 3 for salary.

Query:

SELECT * FROM employee
ORDER BY 2 DESC;

Output:

Explanation: The result is sorted by name in descending order. The output after applying ORDER BY 2 DESC will give the above table as an output. Further, if you notice in the ORDER BY clause, we have specified 2 which means the following SQL Query will sort the data based on the name column.

Advanced Sorting Techniques

PL/SQL also supports more sophisticated sorting techniques, such as sorting by expressions or case-insensitive sorting. You can use expressions in the ORDER BY clause to sort based on calculated values or conditions. For example, you might sort employees by their adjusted salaries, where the adjustment could be based on bonuses or other factors.

Sorting Names Without Case Sensitivity

This query sorts the employee names in a case-insensitive manner by converting all names to lowercase for comparison. It ensures consistent alphabetical ordering regardless of original letter casing. The query sorts employee names without considering whether they are uppercase or lowercase.

By converting all names to lowercase using LOWER(name), it ensures that names are listed in alphabetical order consistently, regardless of how they were originally entered. This way, "Aryan" and "aryan" will be treated the same.

Query:

SELECT * FROM employee
ORDER BY LOWER(name);

Output:

Explanation: The output shows employees' names sorted alphabetically, treating all names as if they are in lowercase. This means names like "Aryan" and "bobby" are listed in a standard alphabetical order. The salaries are shown next to each name but do not affect the sorting

Sorting Based on Calculated Columns

The query creates a new column called adjusted_salary, which is calculated as half of each employee’s original salary (salary * 0.5). It then sorts the employees by this adjusted_salary in descending order, so the employees with the highest adjusted salaries are listed first.

Query:

SELECT id, name, salary * 0.5 AS adjusted_salary
FROM employees
ORDER BY adjusted_salary DESC;

Output:

Explanation: The output displays employees sorted from highest to lowest based on their adjusted_salary. Sam has the highest adjusted_salary because his original salary is the highest, so he appears at the top of the list. Bobby follows, then Ritika, and Aryan is last, reflecting their progressively lower adjusted salaries.

Key Points About ORDER BY Clause in PL/SQL

• Default Sorting Order: The default sorting order is ascending (ASC), so specifying ASC is optional.
  
• Multiple Columns: You can specify multiple columns for sorting, and the order of these columns affects the final sorting.
  
• Performance: Sorting large datasets can impact performance. Ensure appropriate indexes are used to optimize queries.
  
• Integration: The ORDER BY clause can be used with other clauses like WHERE, GROUP BY, and HAVING for refined data retrieval

Conclusion

The ORDER BY clause is an essential component in PL/SQL for controlling the order of query results. By using this clause, developers can enhance the readability and usefulness of their data, whether by arranging records by a single column or employing more advanced techniques like case-insensitive sorting or sorting by calculated columns.