SQL - Multiple Column Ordering

SQL is the standard language used for managing and manipulating databases. One of its powerful features is the ability to sort data using the ORDER BY clause, allowing us to arrange the query results in a meaningful way. By default, SQL sorts data in ascending order, but we can customize it to sort in descending order as well.

In this article, we will explain how to use the ORDER BY clause to sort data by one or more columns, demonstrate how to sort in both ascending and descending order, and explore techniques for sorting with multiple criteria to enhance query results.

ORDER BY Clause in SQL

The ORDER BY clause is used to sort the result set of a query in ascending (ASC) or descending (DESC) order. The syntax for ordering data is simple and straightforward. It sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Syntax:

SELECT * FROM table_name ORDER BY column_name;

Key Terms

• ASC (Ascending): This is the default sorting order, sorting data from the smallest to the largest value (e.g., 1 to 10 or A to Z).
  
• DESC (Descending): This sorts the data from the largest to the smallest value (e.g., 10 to 1 or Z to A).

Sorting by Multiple Columns

When sorting by multiple columns, the records are ordered first by the first column, then by the second, and so on. The first column listed in the ORDER BY clause will determine the primary sorting, and subsequent columns will determine the secondary and further sorting.

Syntax:

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

We will use the geeksforgeeks table in our geeks database to demonstrate the usage of the Multiple Column Ordering. This will help illustrate how to sort data based on more than one criterion, allowing us to achieve more refined and meaningful query results.

Query:

 CREATE TABLE geeksforgeeks(
 FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), 
 CITY VARCHAR(20), AGE INT, GENDER VARCHAR(20));

INSERT INTO geeksforgeeks VALUES
('ROMY', 'Kumari', 'New Delhi', 22, 'female'),
('Pushkar', 'jha', 'New Delhi', 23, 'male'),
('Sujata', 'jha', 'Bihar', 30, 'female'),
('Roshini', 'Kumari', 'Bihar', 16, 'female'),
('Avinav', 'Pandey', 'New Delhi', 21, 'male'),
('Aman','Dhattarwal','Banglore', 30, 'male'),
('Aman','Agnihotri','Chennai', 23, 'male'),
('Aman','Malik','Agra', 35, 'male'),
('Bhawna','Dhattarwal','Banglore', 34, 'female'),
('Bhawna','Meena','Rajastha', 30, 'female')

SELECT * FROM geeksforgeeks;

Output

Example 1: Sorting by First Name and Age

This query sorts the table first by FIRSTNAME in ascending order, and then by AGE in ascending order for records with the same first name. It ensures that employees are listed alphabetically by name and then by their age in increasing order. If there are multiple records with the same first name, it then sorts by AGE.

Query:

 SELECT * FROM geeksforgeeks ORDER BY FIRSTNAME, AGE;

Output

Example 2: Sorting by First Name, Last Name (Descending), and Age (Descending)

This query sorts the data first by FIRSTNAME in ascending order, then by LASTNAME in descending order for records with the same first name, and finally by AGE in descending order for records with the same last name. This ensures that the list is ordered by name, followed by the most recent ages, with a reverse order for last names.

Query:

 SELECT * FROM geeksforgeeks ORDER BY FIRSTNAME, LASTNAME DESC, AGE DESC ;

Output

Conclusion

The ORDER BY clause in SQL is a powerful feature that helps to organize query results based on one or more columns. By using ASC for ascending order and DESC for descending order, we can structure our data in a way that best suits our analysis or reporting needs. Whether we are sorting by a single column or multiple columns, this feature helps bring clarity to our results. . It also allows for greater flexibility when handling large datasets, ensuring that the data is displayed in the most meaningful order.