How to sort multiple columns using ORDER BY keyword in SQL

Overview

In SQL, we use the ORDER BY keyword to sort columns in ascending or descending order. The default is ascending order.

How does it work? It first sorts the first column, then the next, and the next, etc.

Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s);

Example

Let’s assume we have a Person table with columns such as name, age, state, and gender.

Now, we want to get the name and ages of people in the Person table in the order of their age and alphabetically by name.

How do we get this information from our table?

The following code shows how to use the ORDER BY keyword to sort multiple columns in SQL.

CREATE TABLE Person (
    ID int,
    name varchar(100),
    age int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Person
VALUES (1,'Sharon Peller','16','Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul Dons','20','Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera Abedayo','28','Female','Imo');
INSERT INTO Person
VALUES (4,'Maria Elijah','25','Female','Lagos');
INSERT INTO Person
VALUES (5,'David Hassan','30','Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola Disu','28','Female','Lagos');
INSERT INTO Person
VALUES (7,'Praise Dominion','26','Female','Abuja');
INSERT INTO Person
VALUES (7,'Divine Favour','29','Female','Abuja');
INSERT INTO Person
VALUES (7,'Praise Dominion','31','Female','Lagos');
INSERT INTO Person
VALUES (8,'Joe Smith','16','Male','Lagos');
-- Query 
SELECT age, name
FROM Person
ORDER BY age, name;

Explanation

  • Lines 1–7: We create a Person table with columns id, name, age, gender, and state.
  • Lines 10–29: We insert data into the Person table.
  • Lines 31–35: We filter data based on some conditions using the ORDER BY keyword to sort the columns.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)