Problem
How to sort multiple columns in SQL and in different directions?
Input
Let’s create a table named Employees with columns: id, first_name, last_name, and salary.
CREATE TABLE Employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100),
last_name VARCHAR(100),
salary DECIMAL(10, 2)
);
-- Insert data into the table
INSERT INTO Employees (first_name, last_name, salary) VALUES
('John', 'Doe', 50000),
('Jane', 'Smith', 75000),
('Bill', 'Johnson', 60000),
('Alice', 'Johnson', 72000),
('Eve', 'Doe', 65000);
Input Table (Employees):
| id | first_name | last_name | salary |
|---|---|---|---|
| 1 | John | Doe | 50,000 |
| 2 | Jane | Smith | 75,000 |
| 3 | Bill | Johnson | 60,000 |
| 4 | Alice | Johnson | 72,000 |
| 5 | Eve | Doe | 65,000 |
Let’s sort the results based on last_name in ascending order and then by salary in descending order.
Desired Solution
| id | first_name | last_name | salary |
|---|---|---|---|
| 5 | Eve | Doe | 65,000 |
| 1 | John | Doe | 50,000 |
| 4 | Alice | Johnson | 72,000 |
| 3 | Bill | Johnson | 60,000 |
| 2 | Jane | Smith | 75,000 |
Solution:
Sort multiple columns in SQL and in different directions
SELECT * FROM Employees
ORDER BY last_name ASC, salary DESC;
Output:
| id | first_name | last_name | salary |
|---|---|---|---|
| 5 | Eve | Doe | 65,000 |
| 1 | John | Doe | 50,000 |
| 4 | Alice | Johnson | 72,000 |
| 3 | Bill | Johnson | 60,000 |
| 2 | Jane | Smith | 75,000 |
Explanation:
To sort multiple columns in SQL, you can specify the columns you want to sort by in the ORDER BY clause, separated by commas. You can also specify the sorting direction for each column using the ASC (for ascending) or DESC (for descending) keyword.
When you run the above query on the provided input table, the results will be sorted first by the last_name in ascending order and then by salary in descending order for records with the same last name.
