SQL - Multiple Column Ordering
Last Updated :
07 Feb, 2025
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.
Similar Reads
PySpark - Order by multiple columns
In this article, we are going to see how to orderby multiple columns in  PySpark DataFrames through Python. Create the dataframe for demonstration:Python3 # importing module import pyspark # importing sparksession from pyspark.sql module from pyspark.sql import SparkSession # creating sparksession a
4 min read
How to Delete Column in SQL
In SQL, deleting a column from an existing table is a straightforward process, but it's important to understand the implications and the correct syntax involved. While there is no direct DELETE COLUMN command in SQL, we can achieve this by using the ALTER TABLE command combined with DROP COLUMN.In t
5 min read
Selecting Multiple Columns Based On Condition in SQL
SQL (Structured Query Language) is used to manage and query databases. One common requirement when querying data is selecting multiple columns based on specific conditions. Understanding how to use SQL for this purpose can enhance your ability to retrieve relevant data efficiently. In this article,
4 min read
sp_columns - SQL Server
In SQL Server, managing and understanding database schemas is crucial for effective database administration and development. The sp_columns stored procedure is a valuable tool for retrieving detailed metadata about the columns of a specified table or view. In this article, We will learn about sp_col
6 min read
How to Alter Multiple Columns at Once in SQL Server?
In SQL, sometimes we need to write a single query to update the values of all columns in a table. We will use the UPDATE keyword to achieve this. For this, we use a specific kind of query shown in the below demonstration. For this article, we will be using the Microsoft SQL Server as our database an
3 min read
Rename column SQL Server 2008
Renaming a column in a database is a common task that often arises when users want to change the database schema. In SQL Server 2008, renaming columns can be done efficiently using the sp_rename system-stored procedure, as the ALTER TABLE RENAME COLUMN syntax is not supported in this version. This a
4 min read
How to Order PysPark DataFrame by Multiple Columns ?
In this article, we are going to order the multiple columns by using orderBy() functions in pyspark dataframe. Ordering the rows means arranging the rows in ascending or descending order, so we are going to create the dataframe using nested list and get the distinct data. orderBy() function that sor
2 min read
PL/SQL Right Join
In the area of database management, efficiently retrieving and combining data from multiple tables is essential. Among these techniques, the RIGHT JOIN is particularly useful because it includes all records from one table, even when there are no corresponding records in another table.PL/SQL Right Jo
4 min read
How to SELECT DISTINCT on Multiple Columns in SQL?
In the world of databases, data duplication can lead to confusion and inefficiency. SQL provides a powerful tool, SELECT DISTINCT, to retrieve unique values from columns. However, when dealing with multiple columns, the approach becomes more detailed. In this article, we will explain how to use SELE
4 min read
PL/SQL ORDER BY Clause
In PL/SQL, the ORDER BY clause is a vital tool that allows for the sorting of query results by one or more columns, either in ascending or descending order. In this article, We will learn about ORDER BY clause in PL/SQL, its syntax, functionality, and practical usage through examples.Understanding O
7 min read