ORDER BY in PostgreSQL – Different Ways to Sort the Output Results Easily

The SELECT statement is the most widely used SQL method to find and retrieve data from databases according to particular criteria. No matter which RDBMS you are using, you will need this command. The PostgreSQL system we will be dealing with in this article is not an exception. However, there is a catch.

The SELECT command returns results in no particular order. We must perform additional data manipulation to get results in the desired order. Of course, it is rather inconvenient. Therefore, we introduce the ORDER BY query in dbForge Studio for PostgreSQL – it sorts records retrieved by the SELECT statement.

Contents

• PostgreSQL ORDER BY overview
• PostgreSQL ORDER BY clause examples
• Using PostgreSQL ORDER BY clause to sort rows by default
• Sorting rows by one column in descending order
• Using both ASC and DESC attributes
• Set limits for the results retrieved by PostgreSQL ORDER BY
• Sorting rows by multiple columns
• Using PostgreSQL ORDER BY clause to sort rows by date
• PostgreSQL ORDER BY clause and NULL
• Sorting with GROUP BY and ORDER BY
• PostgreSQL ORDER BY random function
• Conclusion

PostgreSQL ORDER BY overview

Before we start diving deeply into different scenarios of using the PostgreSQL ORDER BY command, let’s take out time to consider the basics. This PostgreSQL sort by task may involve various criteria and conditions we’ll review further. However, first things first.

The syntax of the ORDER BY clause used in the SELECT query is as follows:

SELECT columns
FROM tables
[WHERE conditions]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

• Columns – list of columns we retrieve using the SELECT statement.
• Tables – particular tables we are going to retrieve the records from. We must specify at least one table in the query.
• WHERE is an optional condition that needs to be met when retrieving records.
• ASC is a command used to sort the results in ascending order. Adding this condition is optional, as it is the default way to sort the query results in Postgres.
• DESC is a command used to sort the results in descending order. Unlike ASC, we must define DESC explicitly when we want the Postgres ORDER BY command to return the results in descending order.

The statement works according to the following pattern: 

• Starts with the FROM clause to figure out where to look.
• Proceeds to SELECT to define the columns we are looking for.
• Runs the ORDER BY clause.

Now, let us examine the ORDER BY performance with examples.

PostgreSQL ORDER BY clause examples

The process can seem quite simple when you order the standard data in columns in ascending or descending order. However, there might be some pitfalls not to fall into when things are getting more diverse and complicated. This article will review the most common examples of data sorting, from the simplest, standard scenarios to more complicated ones requiring additional consideration.

In this article, we are going to use dbForge Studio for PostgreSQL to demonstrate the various examples of how to use the ORDER BY command. This solution provides functionality for PostgreSQL data reporting, data editing, data import and export, building pivot tables, and master-detail relations. With this convenient and user-friendly IDE, you will be able to create, develop, and execute queries, easily edit and adjust the code according to your needs.

1. To begin with, download the solution from the Devart website and go through the installation steps.

2. Then, on opening the IDE for the first time, fill in the Database Connection Properties.

3. (Optional) Click Test Connection.

4. Click Connect.

Using PostgreSQL ORDER BY clause to sort rows by default

As we already know, Postgres ORDER BY ASC and Postgres ORDER BY are pretty much the same thing. If there aren’t any additional parameters, Postgres shows the selected results sorted in the default ascending order.

Let us imagine our childhood dreams are finally true and we are working in the movie production industry. Today, we need to retrieve the first and last names of the actors we work with. We also want to sort them by their first names in ascending order.

1. In dbForge Studio for PostgreSQL, click New SQL.

2. Enter the following query:

SELECT actor_id, first_name, last_name
FROM actor 
ORDER BY first_name;

3. Click Execute or press F5.

In the output, we see the results of using the ORDER BY command: all the actors’ names are sorted in alphabetical order by the first name column.

Sorting rows by one column in descending order

Now let us assume we need to see the same set of actors, however, displayed in descending order. Thus, we refer to the Postgres ORDER BY DESC parameter and state it explicitly.

The syntax should look like this:

SELECT actor_id, first_name, last_name
FROM actor 
ORDER BY first_name DESC;

By adding the descending Postgres parameter into the ORDER BY clause, we alter the output to look like this:

Using both ASC and DESC attributes

When sorting the results of a query, you can use both ASC and DESC parameters in a single SELECT statement. Though PostgreSQL applies the ASC parameter by default if we don’t specify any other conditions, you can still put it directly into the statement without causing any errors. In other words, using both ASC and DESC PostgreSQL parameters is the correct syntax. For example:

SELECT title, release_year, rating
FROM film
ORDER BY release_year DESC, rating ASC;

This PostgreSQL ORDER BY clause will sort the returned results in two columns. The primary sorting will take place in the release_yesr column, the records will appear in descending order. The secondary sorting applies to the rating column and puts the results in ascending order.

Set limits for the results retrieved by PostgreSQL ORDER BY

The LIMIT parameter in PostgreSQL restricts the number of rows shown in the query output. It is helpful when we deal with a lot of data, and we need only a part of all results. The combination of ORDER BY and LIMIT will define the size of the data portion to be returned.

The syntax example is below:

SELECT title, description, release_year
FROM film
WHERE release_year = '2006'
ORDER BY title
LIMIT 5;

The output will display the results sorted by the movie title in ascending order – the default way. The limit is applied and, even though the film table may include hundreds of records, we only view the top-5 results.

Sorting rows by multiple columns

The above examples are related to sorting the output data in one column. However, different work scenarios require sorting results in several columns at once. Usually, it is necessary when we have identical values in columns.

The cases when we need to use the Postgres ORDER BY with multiple values are quite common. Although the most common scenario is dealing with the two-columns cases, you can add as many of those as you wish.

The standard syntax is as follows:

SELECT column1, column2, column3
FROM table
ORDER BY column2, column3;

The most essential factor is the order of columns after ORDER BY. It determines how the query sorts the results. In the example above, column2 is primary. The results will be sorted according to column2 in ascending order (the default one). However, if there are several identical values in column2, they will be sorted according to the values in column3, also in ascending order.

SELECT title, release_year, rating
FROM film
ORDER BY release_year, rating;

The output:

As you can see, some records have the same values in the release_year column. They are sorted additionally by the rating values, in ascending order.

This PSQL ORDER BY command variation allows sorting records in different columns. Let us see another example of the command but with the DESC parameter this time.

SELECT title, release_year, rating
FROM film
ORDER BY release_year, rating DESC;

Let’s check the output of the query with the DESC parameter applied.

We can see that the movies with the same release year are now sorted in descending order according to the rating column.

Using PostgreSQL ORDER BY clause to sort rows by date

One of the most frequent practical scenarios is sorting the data by date. In PostgreSQL, we use the following syntax to accomplish this goal:

SELECT column1, column2, column3
FROM table
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];

Let’s see how PostgreSQL goes about sorting the query results according to the date. Assume we have a list of the movies that have been updated with a description or some new additional materials. Today, we need to see the newest and the oldest updates:

SELECT title, last_update
FROM film
ORDER BY last_update;

Here the result is:

This example shows the simplest case – the query syntax only includes the basic, mandatory parameters. The results are sorted in ascending order, as it happens by default. We can also reverse this order by applying the DESC parameter:

SELECT title, last_update
FROM film
ORDER BY last_update DESC;

The result is:

PostgreSQL ORDER BY clause and NULL

NULL indicates missing or unknown data, and this type of value can be often found in databases. We need to consider them when sorting the data. The ORDER BY clause presupposes putting NULLs either before or after non-null values. To achieve that we use the NULLS FIRST or NULLS LAST option.

The default ASC order means that NULLS LAST is applied automatically. If you specify DESC in your query directly, respectively, you’ll get NULLS FIRST.

The standard query for the data that includes nulls would look as follows:

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY last_name NULLS LAST;

The result would be:

In case we need NULLS to be on the very top of the query results, before the non-null values, we need to use the NULL FIRST option:

SELECT actor_id, first_name, last_name
FROM actor
ORDER BY last_name NULLS FIRST;

The result:

If your data include NULLs, the ORDER BY clause in Postgres will sort them anyway, regardless of whether you added NULLS FIRST or NULLS LAST options in the query syntax or not.

Sorting with GROUP BY and ORDER BY

GROUP BY is another clause used in PostgreSQL for sorting the query results. It groups the data according to the specified criteria. Therefore, both ORDER BY and GROUP BY perform similar functions. However, there is a difference.

The ORDER BY clause sorts the query result by one or several columns and presents them in ascending or descending order. While the GROUP BY clause uses aggregate functions to arrange data into groups. It relates to columns containing identical values in different rows. When the system defines such values, the GROUP BY clause unites those rows into a group.

Such a difference also allows us to use both these clauses in one query – the PostgreSQL GROUP BY ORDER BY combinations are common in many scenarios.

Note: Keep the correct order of elements in a SELECT statement when you apply both ORDER BY and GROUP BY in PostgreSQL: GROUP BY must always come before ORDER BY

The basic syntax of the query containing both these clauses is as follows:

SELECT column1, column2, .....columnN    
FROM table_name        
GROUP BY column1, column2, ...columnN 
ORDER BY column1, column2, ...columnN

Assume that we are going to count the number of movies in each rating category and then view the data conveniently. The combination of GROUP BY and ORDER BY will suit perfectly in this case.

SELECT rating,
COUNT(*) "number"
FROM film
GROUP BY rating
ORDER BY COUNT(*);

The result will be as follows:

This example illustrates the most basic case. The query syntax can be more complicated and involve several aggregate functions and conditions to provide the results as precise and convenient for the users as possible.

PostgreSQL ORDER BY random function

ORDER BY random() comes in handy quite often for PostgreSQL operations. Applying this function lets you get a random one or more rows from any table. Usually, it serves for getting example data from large tables. The LIMIT clause is also frequent in ORDER BY random Postgres queries because it helps retrieve data portion of the desired size.

The basic syntax of the query is as follows:

SELECT column
FROM table 
ORDER BY random() 
LIMIT 1;

However, note that PSQL ORDER BY random type is not the most efficient way of retrieving the data. PostgreSQL has to retrieve all rows from the target table and select one random row. When the table is small, the query will be executed quickly and easily. Large tables containing lots of data cause difficulties, and developers have to apply various methods to optimize such queries.

In fact, it is one of the most common and painful problems for all database specialists. The larger your project is, the more data it has, the more it slows performance down. All database specialists need to use the query plan before executing that query and check all factors influencing performance. Modern tools like the Query Profiler from Devart let the database pros construct and analyze queries to define the potential bottlenecks and resolve them immediately.

Conclusion

In this article, we have talked about the most popular ways to use the syntax of the SELECT statement with the ORDER BY clause. Now when you have a basic understanding of this clause work, it becomes simpler to write a code, both manually and with the help of professional tools like dbForge Studio for PostgreSQL. The IDE allows you to easily navigate and manage your database, export and import data, significantly reduce the coding time, minimize errors, and so much more.