How to Sort Multiple Columns Using ORDER BY Clause in PostgreSQL

Querying the data from a Postgres table retrieves the data in an unspecified order. To get the table’s data into a particular order the ORDER BY clause is used in the database management systems. Using the ORDER BY clause, we can classify the Postgres tables in ascending or descending order on the basis of single/multiple columns.

This post presents a detailed guide on sorting the table’s data based on multiple columns.

How to Sort Multiple Columns in Postgres?

In Postgres, the ORDER BY clause allows us to sort the table’s data on the basis of multiple columns. The comma-separated syntax is used in the ORDER BY clause to sort the table’s data based on multiple columns:

SELECT col_list
FROM tbl_name
ORDER BY col_1 [ASC | DESC], col_2 [ASC | DESC], …, col_N [ASC | DESC];

Here, col_list represents the table’s columns.
tbl_name represents a table to be sorted.
col_1, col_2, …, col_n represent the columns based on which the table will be sorted.
The parameter ASC or DESC decides the tables’ sorting order, i.e., ascending or descending.

Example 1: Sorting a Table Based on a Single Column

A table named “emp_data” has been created with three columns: emp_id, emp_name, and joining_date. Let’s query the table’s data using the “SELECT” query:

SELECT * FROM emp_data;
img

The output shows that the result set is in an unspecified order. To get the result set in a particular order, use the ORDER BY clause with a specific sorting parameter, i.e., ASC or DESC:

SELECT * FROM emp_data
ORDER BY emp_id ASC;
img

The result has been sorted into ascending order successfully.

Example 2: Sorting a Postgres Table Based on Multiple Columns

Specify the multiple columns in the ORDER BY clause to sort the table’s data based on the specified columns:

SELECT * FROM emp_data
ORDER BY emp_id ASC, emp_name ASC;
img

From the result set, you can observe that the employees who have the same ids have been sorted based on their names. This is how the multi-column sorting works in Postgres.

Example 3: Sorting Multiple Columns Descendingly

Specify the multiple columns in the ORDER BY clause to sort the table’s data based on the specified columns:

SELECT * FROM emp_data
ORDER BY emp_id DESC, emp_name DESC;
img

The output proves that multiple columns have been sorted descendingly.

Conclusion

In Postgres, the ORDER BY clause allows us to classify the Postgres tables in ascending or descending order based on single/multiple columns. The comma-separated syntax is used in the ORDER BY clause to sort the table’s data based on multiple columns. This Postgres blog presented a thorough overview of how to sort table data based on multiple columns using the ORDER BY clause.