In the world of relational databases, SQL (Structured Query Language) is probably the language one will need to use to manage and manipulate data. Sorting of data is one of the basic operations in SQL that helps users to get query results arranged as they want. If sorting on one column is simple enough, there are cases when it is better to sort by multiple columns. In this article, we will see what multi-column sorting is and then some example queries on sorting data according to more than one column.
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 solely by Name may not suffice in scenarios where multiple employees share the same name. In such cases, secondary sorting criteria, like Department or Salary, become essential to achieve a meaningful order.
Syntax Overview:
In SQL, the ORDER BY clause facilitates sorting. To sort by multiple columns, simply specify each column name separated by commas. The syntax is as follows:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC/DESC, column2 ASC/DESC, ...;
- ASC: Ascending order (default).
- DESC: Descending order.
Example Queries of Sorting Data According to More Than One Column
Let’s create a table, insert some data into it, and then see some example queries for sorting data according to more than one column.
-- Create the table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);
-- Insert sample data
INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES
(1, 'Minal Pandey', 'HR', 50000.00),
(2, 'Vardhana Sharma', 'IT', 60000.00),
(3, 'Kavya Sharma', 'Finance', 55000.00),
(4, 'Mahi Pandey', 'HR', 48000.00),
(5, 'Vivek Sharma', 'IT', 62000.00),
(6, 'Mivi Sharma', 'Finance', 57000.00);
Output:
You can see the table content below:
Employees Table
Example 1: Sorting by Department and Salary
Query:
SELECT Name, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;
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.
Output:
Employees are first sorted by their Department in ascending order. Within each department group, employees are then sorted by their Salary in descending order.
Sorting by Department and Salary
Example 2: Sorting by Salary and Name within each Department
Query:
SELECT Name, Department, Salary
FROM Employees
ORDER BY Salary DESC, Name ASC;
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.
Output:
Employees are first sorted by their Salary in descending order. Within each salary group, employees with the same salary are sorted by their Name in ascending order.
Sorting by Salary and Name within each Department
Example 3: Sorting by Department, Salary, and Name
Query:
SELECT Name, Department, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC, Name ASC;
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.
Output:
Employees are first sorted by their Department in ascending order. Within each department group, employees are then sorted by their Salary in descending order. If two employees within the same department have the same salary, they are further sorted by their Name in ascending order.
Sorting by Department, Salary, and Name
Example 4: Sorting by Department in Descending Order, then by Salary in Ascending Order
Query:
SELECT Department, Salary
FROM Employees
ORDER BY Department DESC, Salary ASC;
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.
Output:
Employees are first sorted by their Department in descending order. Within each department group, employees are then sorted by their Salary in ascending order.
Sorting by Department in DESC, then by Salary in ASC
Example 5: Sorting by Salary in Ascending Order, Nulls Last
Query:
SELECT Name, Department, Salary
FROM Employees
ORDER BY Salary ASC NULLS LAST;
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.
Output:
Employees are sorted by their Salary in ascending order. NULL values in the Salary column, if any, are placed last in the sorted results. However, in this case, there are no NULL values in the Salary column, so all non-null values are sorted in ascending order.
Sorting by Salary in Ascending Order, Nulls Last
Handling NULL Values
When multiple columns are used for sorting data, it is a good idea to consider what is done with the NULL values. Without giving any special preference, NULL values are sorted first in ascending order (ASC) and last in descending order (DESC). Still, the SQL function makes it customizable through the NULLS FIRST or NULLS LAST options.
Conclusion
SQL lets one sort the data according to more than one column so that the result of a query is organized in a customized manner to meet specific requirements. You can utilize the ORDER BY clause to specify the sorting criteria which are mainly the basis of business needs. Whether by combining two columns or others, these techniques define and structure the way users interact with the database and translate the data into knowledge.