How to sort multiple columns in SQL and in different directions?

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):

Let’s sort the results based on last_name in ascending order and then by salary in descending order.

Desired Solution

Solution:

Sort multiple columns in SQL and in different directions

SELECT * FROM Employees
ORDER BY last_name ASC, salary DESC;

Output:

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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery
4. SQL Order by

More SQL Questions

1. How to access previous row value in SQL?
2. How to exclude specific columns using select except?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?