How to Perform the PostgreSQL ORDER BY Clause

Introduction

When you perform a query in PostgreSQL, you may want your results to be returned in a certain order. It’s easy to sort the results of a PostgreSQL query using the ORDER BY clause in a SELECT statement. In this article, we’ll take a closer look at the PostgreSQL ORDER BY clause and see some examples of how it’s used in queries.

Prerequisites

In order to follow along with this tutorial and test out our examples of the ORDER BY clause, you’ll need to have access to PostgreSQL and its command-line interface psql. You should also have a basic understanding of database management systems.

Open Command-line Interface

The command-line interface psql can be used to execute SQL statements and queries in PostgreSQL.

To access the interface, you’ll first need the privileges associated with the postgres superuser. Type the following command to elevate your privileges:

Next, enter the psql command:

After you’ve connected to PostgreSQL, you may start executing queries.

PostgreSQL ORDER BY Clause

In PostgreSQL, the ORDER BY clause is used to sort results of a SELECT statement according to your preference.

The ORDER BY clause can sort results in ascending or descending order. If no modifier is supplied, the default sort order is ascending.

Create Database in PostgreSQL

Let’s begin by creating a database in PostgreSQL. To create a new database, we’ll use the following command:

To connect to your database, simply enter \c followed by the name of the new database you created.

Create Table in PostgreSQL

Now that we have a database, our next step will be to create a table within it.

The following syntax is used to create a table in PostgreSQL:

NOTE: The column constraint is optional. It is used to define rules related to the data types in the table and to limit the type of data that can exist in the table.

Insert Data into PostgreSQL

To insert data into a table, we use the basic syntax shown below:

In PostgreSQL, you can insert multiple column values for a record by delimiting them with commas.

The PostgreSQL ORDER BY Clause Syntax and Examples

The syntax used for the ORDER BY clause is:

NOTE: The expression in this syntax refers to the column that you want to retrieve.

Let’s look at this syntax a little more closely. The first thing we do is to specify the columns or expressions from the table_name. You can use multiple columns if you want as long as they’re separated by commas in your statement.

A SELECT statement with an ORDER BY clause may include a WHERE condition to limit the result set to records that satisfy certain conditions.

To demonstrate the ORDER BY clause in our examples, we’ll be using a table named portal as our sample table.

PostgreSQL ORDER BY Using Single Column

In the following PostgreSQL ORDER BY example, we sort the results by stud_id in ascending order:

In no ASC or DESC attribute is specified, the results are returned in ascending order by default. Therefore, you can feel free to omit the ASC modifier from your queries.

PostgreSQL ORDER BY Descending

Let’s look at another example. This time, we use the desc modifier:

This query will return all results sorted by _studid in ascending order.

PostgreSQL ORDER BY Using Multiple Columns

The next PostgreSQL ORDER BY example will sort more than one column. We’ll be sorting the _studid in ascending order and the name field in descending order. The SELECT statement will look like this:

Here’s what the results will look like:

As you can see, the query returns all records sorted by the _studid field in ascending order, but it sorted the name field in descending order.

PostgreSQL ORDER BY using expressions

The next PostgreSQL ORDER BY example sorts rows by expressions. We’ll use the PostgreSQL LENGTH() function to identify the length of the characters in the given string:

This statement will sort the results based on the address field of the portal table using the values returned by the LENGTH() function.

Conclusion

Sorting the results of a PostgreSQL query can make the data easier to read and understand. The ORDER BY clause makes it easy to sort query results in either ascending or descending order. In this article, we looked at multiple examples of the PostgreSQL ORDER BY clause. With these examples, you’ll have a solid understanding of how the ORDER BY clause is used and will be prepared to utilize the clause in your own PostgreSQL queries.