Sorting Data According to More Than One Column in SQL

In the world of relational databases, SQL is the essential tool for managing and manipulating data. One of the most fundamental operations in SQL is sorting data. While sorting by a single column is straightforward, sorting by multiple columns offers more control and flexibility.

In this article, we will explore multi-column sorting in SQL, complete with detailed examples, explanations, and best practices.

Multi-column Sorting

Multi-column sorting consists of organizing search results depending on the values from two or more columns. Let's take a database table containing employee records with columns such as Name, Department, and Salary as an example. Sorting only by Name might not be sufficient when multiple employees share the same name. In such cases, secondary sorting based on Department or Salary ensures that the results are meaningful and well-organized.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC/DESC, column2 ASC/DESC, ...;

Key Terms

• ASC: Sorts the data in ascending order (default).
  
• DESC: Sorts the data in descending order.

Let’s go through some practical examples to understand how to apply multi-column sorting in SQL. We will use an Employees table with the following columns: Name, Department, and Salary.

Example 1: Sorting by Department and Salary

This query sorts employees first by Department in ascending order. Within each department, it sorts by Salary in descending order.

Query:

SELECT Name, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;

Output

Explanation:

• This query retrieves data from the Employees table, specifically the Name, Department, and Salary columns.
  
• It then sorts the results in ascending order based on the Department column and in descending order based on the Salary column.

Example 2: Sorting by Salary and Name within each Department

This query first sorts employees by Salary in descending order. If two employees have the same salary, the query further sorts them by Name in ascending order.

Query:

SELECT Name, Department, Salary
FROM Employees
ORDER BY Salary DESC, Name ASC;

Output

Explanation:

• This query retrieves data from the Employees table, specifically the Name, Department, and Salary columns.
  
• It then sorts the results in descending order based on the Salary column. If two employees have the same salary, it further sorts them in ascending order based on their Name.

Example 3: Sorting by Department, Salary, and Name

This query sorts the data first by Department in ascending order, then by Salary in descending order within each department. If two employees have the same salary, they are further sorted by Name in ascending order.

Query:

SELECT Name, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC, Name ASC;

Output

Explanation:

• This query retrieves data from the Employees table, specifically the Name, Department, and Salary columns.
  
• It then sorts the results first in ascending order based on the Department column. Within each department group, it further sorts the employees in descending order based on their Salary.
  
• If two employees within the same department have the same salary, it finally sorts them in ascending order based on their Name.

Example 4: Sorting by Department in Descending Order, then by Salary in Ascending Order

This query sorts employees by Department in descending order. If two employees belong to the same department, they are sorted by Salary in ascending order.

Query:

SELECT Department, Salary
FROM Employees
ORDER BY Department DESC, Salary ASC;

Output

Explanation:

• This query retrieves data from the Employees table, specifically the Department, and Salary columns.
  
• It then sorts the results in descending order based on the Department column. Within each department group, it further sorts the employees in ascending order based on their Salary.

Example 5: Sorting by Salary in Ascending Order, Nulls Last

This query sorts employees by Salary in ascending order, placing any NULL values at the end of the result set. NULLS LAST ensures that any rows with NULL salary values will appear at the bottom.

Query:

SELECT Name, Department, Salary
FROM Employees
ORDER BY Salary ASC NULLS LAST;

Output

Explanation:

• This query retrieves data from the Employees table, specifically the Name, Department, and Salary columns.
  
• It then sorts the results in ascending order based on the Salary column. Additionally, it specifies that NULL values in the Salary column should appear last in the sorted results.

Handling NULL Values

In SQL, when sorting by multiple columns, NULL values are handled based on the sorting order:

• By default, NULL values are sorted first in ascending order (ASC) and last in descending order (DESC).
  
• We can customize this behavior using NULLS FIRST or NULLS LAST.

Conclusion

In SQL, multi-column sorting allows us to organize our data efficiently by applying multiple levels of sorting criteria. By utilizing multi-column sorting, we can ensure that data is arranged in a hierarchy that fits our specific requirements. This can be particularly important in business contexts where decisions are based on a combination of criteria, such as sorting employees by Department, then by Salary, and finally by Name. mastering multi-column sorting not only improves the presentation of our data but also boosts the efficiency of our queries, making them more aligned with business needs.