In PostgreSQL, the SELECT statement returns a result-set in an unspecified order. To get the sorted rows of a result-set, PostgreSQL offers an ORDER BY clause. The table rows can be sorted ascendingly or descendingly with the help of the ORDER BY clause. The ASC or DESC keyword is used in the ORDER BY clause to sort the table rows in a particular order.

Let’s learn the working of the ORDER BY clause with examples.

How to Use ORDER BY Clause in PostgreSQL?

The ORDER BY clause can sort a single or a list of columns using the comma-separated syntax:

SELECT col_1, col_2, col_3, ..., col_N
FROM tab_name
ORDER BY col_1, col_2, col_3,  .. col_N [ASC | DESC];

Let’s consider the below-listed points to get a basic understanding of the ORDER BY clause:

● The SELECT statement will return a result set in an unspecified order.

● tab_name is a table whose result set will be sorted in ascending/descending order.

● The ORDER BY clause takes an individual column or list of columns and sorts them ascendingly or descendingly.

● A column or list of columns will be sorted according to the parameter specified in the ORDER BY clause i.e. ASC or DESC. HERE ASC represents ascending order, and DESC represents descending order.

ASC is a default option in the ORDER BY clause, so omitting it will sort the selected result set in ascending order.

Practical Implementation of ORDER BY Clause in Postgres

The purpose of this section is to teach you how to sort table rows in ascending/descending order based on an individual column or several columns. A table named "bike_details" has already been created, whose details are as follows:

SELECT * FROM bike_details;

Example # 1: How to Sort a Table in Ascending Order Based on a Single Column?

Suppose we have to sort the bike_details table in ascending order based on the bike_model column. To do so, run the below-given query:

SELECT * FROM bike_details
ORDER BY bike_model ASC;

The above snippet proves that the ORDER BY clause sorted the result set in ascending order based on the bike_model.

Example # 2: How to Sort a Table in Descending Order?

Let’s run the following query to sort the bike_details table in descending order based on the bike_id column:

SELECT * FROM bike_details
ORDER BY bike_id DESC;

The above snippet shows that the ORDER BY clause successfully sorted the result set in descending order with respect to the bike_id column.

Example # 3: How to Use ORDER BY Clause to Sort a Table by Multiple Columns in PostgreSQL?

Let’s run the following query to sort the bike_model column in descending order and the bike_color column in ascending order:

SELECT bike_model, bike_color
FROM bike_details
ORDER BY bike_model ASC, bike_color DESC;

● In this example, we utilized the ORDER BY clause to sort the bike_model in ascending order and bike_color in the descending order.

● So, the ORDER BY clause firstly sorted the rows in ascending order according to the bike_model, and afterward, it sorted the rows in descending order according to the bike_color column.

● As shown in the output, bikes with the same bike_model are sorted according to the bike_color column, i.e., in descending order.

That was all the basic information regarding the Postgres ORDER BY clause.

Conclusion

To get the sorted rows of a result-set, PostgreSQL offers an ORDER BY clause. The ORDER BY clause in Postgres enables us to sort the table rows in ascending/descending order. To sort the result-set in a specific order, specify the ORDER BY clause and column name followed by the DESC or ASC keywords. This write-up considered several use cases of the ORDER BY clause and explained them with the help of suitable examples.