How to Perform the PostgreSQL ORDER BY Clause

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
sudo su - postgres

Next, enter the psql command:

1
psql

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:

1
CREATE DATABASE test_db;

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:

1
CREATE TABLE TABLE_NAME (column_name + datatype + CONSTRAINT [optional]);

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:

1
2
INSERT INTO TABLE_NAME (COLUMN_1,COLUMN_2...,)
VALUES (VALUES_1,VALUES_2...,)

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:

1
2
SELECT expressions FROM TABLE_NAME
ORDER BY expression [ASC  |  DESC];

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.

1
2
3
4
5
6
7
8
 stud_id |  name  | address |   remarks  
---------+--------+---------+-------------
       2 | cayl   | london  | good
       3 | daryl  | gensan  | outstanding
       4 | lovely | mexico  | very_good
       5 | kurt   | busan   | good
       1 | alexis | spain   | poor
(5 ROWS)

PostgreSQL ORDER BY Using Single Column

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

1
SELECT stud_id,name FROM portal ORDER BY stud_id ASC;

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.

1
2
3
4
5
6
7
8
 stud_id |  name  
---------+--------
       1 | alexis
       2 | cayl
       3 | daryl
       4 | lovely
       5 | kurt
(5 ROWS)

PostgreSQL ORDER BY Descending

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

1
2
3
4
5
6
7
 stud_id |  name  
---------+--------
       5 | kurt
       4 | lovely
       3 | daryl
       2 | cayl
       1 | alexis

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:

1
2
SELECT stud_id,name FROM portal
ORDER BY stud_id ASC,name DESC;

Here’s what the results will look like:

1
2
3
4
5
6
7
8
 stud_id |  name  
---------+--------
       1 | alexis
       2 | cayl
       3 | daryl
       4 | lovely
       5 | kurt
(5 ROWS)

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:

1
2
3
SELECT address,LENGTH(address)len
FROM Portal
ORDER BY LENGTH(address)DESC;

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.