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