PostgreSQL Ascending Order and Descending Order

Introduction

When you retrieve data in PostgreSQL, you’ll probably want your results to be sorted in a certain way. In PostgreSQL, you can specify the sort order of your results with the ORDER BY clause. This clause allows you to specify the column by which you’d like to sort your results, and it also allows you to choose between ascending order and descending order. In this article, we’ll take a closer look at the PostgreSQL ascending order and descending order options.

Prerequisites

In order to follow along with the examples we’ll be looking at in this tutorial, you’ll need to ensure that PostgreSQL server is properly installed and configured. The service must be running in the background.

If you’re working in a Linux or Windows environment, you can download PostgreSQL here.

What is the PostgreSQL ORDER BY Clause?

Whenever we want to retrieve data from a PostgreSQL table, we create a SELECT statement. By default, PostgreSQL will return the rows in the same order that they were inserted. However, we can tweak our results to make them more readable by sorting them. There are two ways that the results can be sorted: ascending order and descending order. We use the PostgreSQL ORDER BY clause in our SELECT statement to specify our sorting preferences.

Shown below is the syntax for the PostgreSQL ORDER BY clause:

Let’s discuss this syntax in a bit more detail:

• First, we specify the column name that we’ll use for sorting in the ORDER BY clause. If we want to sort by a number of columns, we separate them using a comma.
• After we specify our column name, we use ASC to specify ascending order or DESC for descending order. The default value of the ORDER BY clause is ASC or ascending order if no value is provided.

Creating A Sample Data Set

We’ll need to create a sample data set to use in our examples. Let’s start by creating a table named client_info:

Now, let’s put some records in the table:

We can verify that our INSERT was successful with a SELECT query statement:

An Example Using PostgreSQL ORDER BY ASC

Now that we’ve learned how the PostgreSQL ORDER BY statement works, we’ll try using it to sort the records that we created in the previous section.

Let’s use the following statement:

This statement will instruct PostgreSQL to select all rows in the table client_info and sort them based on the client_firstname column in ascending order.

The results of this query will look something like the following:

We can see that the values in the client_firstname field are sorted in ascending alphabetical order– our query was successful.

An Example Using PostgreSQL ORDER BY DESC

In the previous example, we used the ORDER BY clause with the ASC option for ascending order. Now, we’ll do the opposite and use ORDER BY with DESC to sort in descending order.

To do this, we’ll use the following statement:

The code shown above will sort the retrieved rows in descending order based on client_age.

The results will look something like this:

As we expected, the values in the client_age column are sorted and returned in descending order.

Conclusion

Sorting your results can bring order and readability to disorganized data, and it’s important to know how to specify your sort preferences in your database queries. In this tutorial, we talked about the PostgreSQL ORDER BY clause, which allows you to choose between ascending order and descending order as your sort options. With the examples we discussed earlier as a guide, you’ll be able to specify sort order in your own database queries.

Just the Code

Shown below are all the code snippets we looked at throughout our tutorial. Feel free to copy and modify them to fit your own use cases.