PostgreSQL Order by clause

In this section, we are going to learn the PostgreSQL ORDER BY condition, which is used for sorting data in ascending or descending order. And the records are fetched on the basis of one or more columns.

When fetching the records from a table, the SELECT command returns rows in an undetermined order. For this, we will use the ORDER BY clause in the SELECT statement for sorting the rows of the result set.

The ORDER BY clause permits us to sort rows returned by a SELECT condition in ascending or descending order depends on the sort expression.

The syntax of PostgreSQL Order by clause

The syntax of PostgreSQL ORDER BY condition is as below:

SELECT column-list  
FROM table_name  
[WHERE condition]  
[ORDER BY column1, column2, .. columnN] [ASC | DESC];  

Below are the parameters which are used in the above command:

The order by clause works in the below format:

It begins from the From clause → after that it will Select the particular columns → and then perform by the Order by clause.

Note: In the ORDER BY clause, we can use the column alias in the SELECT command because of the order assessment.

Examples of PostgreSQL ORDER BY clause

Here, we will understand the working of PostgreSQL ORDER BY clause with the help of below examples:

For this, we are going to take the Employee table, which we created in the earlier section of the PostgreSQL tutorial.

The below screenshot defines the different columns present in the Employee table:

In the above table, we will perform different types of operators using the order by clause.

• To sort rows by one column using PostgreSQL ORDER BY clause

In the below example, we will use the ORDER BY condition to get employee by their first_name in ascending order:

SELECT first_name, last_name
FROM employee
ORDER BY first_name ASC;

Output

After executing the above command, we will get the below output, which fetches all the first_name in the ascending order.

We can also ignore the ASC option in the ORDER BY clause because ASC option is default as we can see in the below command:

SELECT first_name, last_name
FROM employee
ORDER BY first_name ;

Output

As we can see in the below screenshot that we are getting a similar output as above:

To sort rows by one column in descending order using PostgreSQL ORDER BY clause

The below command selects the first _name and last _name from the Employee table and gets the rows by values in the last_name column in descending order:

SELECT first_name, last_name
FROM employee
ORDER BY first_name desc ;

Output

After executing the above command, we will get the below output, which fetches all the first_name in the descending order.

To sort rows by multiple columns using PostgreSQL ORDER BY clause

The below command is used to select the first_name and last_name from the employee table and sorts the rows with multiple columns:

SELECT address, email
FROM employee
ORDER BY address ASC, email DESC;

Output

After executing the above command, the ORDER BY clause firstly fetches the rows by values in the first_name column. And then it fetches the rows by values in the last_name column.

In the below image, we can also see that we have two employees who're having a similar first_name (john) and the last_name sorted in the descending order.

To sort rows by expressions using PostgreSQL ORDER BY clause

In the below example, we will select the address and their lengths, which gets the rows by the address's lengths in the employee table:

Note: In PostgreSQL, the column alias len is accessible, and used in the ORDER BY clause as the ORDER BY clause is done after the SELECT condition. And the LENGTH() function is used to take a string and gets the length of particular string.

SELECT address,
LENGTH (address) len
FROM employee 
ORDER BY len DESC;

Output

After executing the above command, we will get the below result, which describes the address column's length value in the employee table.

PostgreSQL ORDER BY Condition and NULL

When we are fetching the rows which having NULL values, we can define the order of NULL with further LAST options of the ORDER BY clause.

In the database, NULL is a representation that defines the missing records or those unspecified at the time of recording.

ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST]

In the above syntax, we have two options which are the most importantly used:

For our better understanding, we will create a table as follows:

CREATE TABLE demo(
	num varchar
);

After executing the above command, we will get the below message window; the demo table has been created successfully.

Once we created a table, we will insert some values in the demo table as below:

INSERT INTO demo(num)
VALUES(1),(2),(null),(3),(4);

Output

After executing the above command, we will get the below message window; the values have been inserted successfully in the demo table.

Note: Here, we don't need to understand the CREATE TABLE and INSERT command because we want to execute the command either in pgAdmin or psql to create the demo table and insert the values into it.

The below command is used to fetch the records from the demo table:

SELECT num
FROM demo
ORDER BY num;

Output

After executing the above command, we will get the output:

In this example, we use the ORDER BY condition to get values in the demo table's ascending order in the num column, which places NULL after another value. Therefore, by default the ORDER BY clause takes the NULLS LAST option if we use the ASC option.

So, the below command is used to fetch a similar result:

SELECT num
FROM demo
ORDER BY num NULLS LAST;

Output

After implementing the above command, we will get the below outcome:

And we will use the NULLS FIRST option to place NULL before further non-null values as follows:

SELECT num
FROM demo
ORDER BY num NULLS FIRST;

Output

After executing the above command, we will get the below result:

Then, we will get values in the descending order of the demo table in the num column with the below command's help:

SELECT num
FROM demo
ORDER BY num DESC;

Output

Once we implemented the above command, we will get the below output:

After executing both the ASC and DESC command, we can see the output that, by default, the ORDER BY condition with the DESC option uses the NULLS FIRST option.

Reverse the order

We will use the NULLS LAST option to reverse the order in the particular table.

SELECT num
FROM demo
ORDER BY num DESC NULLS LAST;

Output

After executing the above command, we will get the below outcome:

Overview

• We use the ORDER BY clause in the SELECT command to fetch rows in a particular table.
• We use the ASC option to get rows in ascending order as well as the DESC option for fetching the rows in descending order.
• By default, the ORDER BY condition uses the ASC function.
• We use NULLS FIRST and NULLS LAST options for describing the order of NULL with another non-null value.