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:
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.
In the below example, we will use the ORDER BY condition to get employee by their first_name in ascending order:
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:
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:
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:
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.
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.
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:
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:
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:
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:
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:
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:
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.
After executing the above command, we will get the below outcome: