Javatpoint Logo
Javatpoint Logo

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:

Parameter Description
column_list It is used to define the columns or calculations, which we want to retrieve.
table_name It is used to describe the tables that we want to retrieve records from. And there must be at least one table listed in the FROM clause.
WHERE conditions It is an optional parameter used to define the condition that must be fulfilled to retrieve records.
ASC It is also an optional parameter, and it is used to sort the result set in ascending order by expression (default, if no modifier is the provider).
DESC It is also an optional parameter, and it is used to sort the result set in descending order by expression.

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.

PostgreSQL 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:

PostgreSQL Order by clause

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:

Output

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

PostgreSQL Order by clause

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:

Output

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

PostgreSQL Order by clause

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:

Output

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

PostgreSQL Order by clause

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:

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.

PostgreSQL Order by clause

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.

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 clause

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:

Options Description
Nulls First It places Null before further non-null values.
Nulls Last It places Null after another non-null value.

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.

PostgreSQL Order by clause

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

Output

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

PostgreSQL Order by clause

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:

Output

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

PostgreSQL Order by clause

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:

Output

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

PostgreSQL Order by clause

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

Output

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

PostgreSQL Order by clause

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

Output

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

PostgreSQL Order by clause

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.

Output

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

PostgreSQL Order by clause

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.





Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA