Postgres ORDER BY DESC

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

Introduction

When you retrieve information from a PostgreSQL table, you may want your results to be returned in a particular order. The PostgreSQL ORDER BY keywords allow you to sort records from the database on the column of your choice. You can also specify whether you want your results sorted in ascending or descending order. In this article, we’ll show you how to sort records and return them in descending order using the Postgres ORDER BY DESC keywords.

Prerequisites

You’ll need to make sure the following prerequisites are in place before proceeding with this tutorial:

  • PostgreSQL needs to be installed on your computer in order to follow along with our PostgreSQL ORDER BY DESC examples.
  • You should have some basic knowledge of PostgreSQL to get the most out of this article.

Postgres ORDER BY descending

Let’s begin by looking at the basic syntax of the ORDER BY keywords with the DESC clause:

1
SELECT * FROM TABLE_NAME ORDER BY column_name DESC

NOTE: If you’d like to have your results returned in ascending order instead, just replace the DESC keyword with ASC.

Postgres ‘ORDER BY’ example

Before we will proceed with our example, let’s create a table and name it “richest_countries”:

1
2
3
4
5
6
CREATE TABLE richest_countries(
    id SERIAL PRIMARY KEY,
    country VARCHAR(30),
    continent VARCHAR(30),
    gdp_per_capita NUMERIC
);

NOTE: Be sure to create a database for your sample table above before executing the CREATE TABLE statement shown above. Once you’ve created your database, you can use the \c command in psql, followed by the database name, to connect to it.

Now let’s insert a few records into the table. This will provide us with some sample data to use in our examples:

1
2
3
4
5
6
7
8
9
10
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Australia', 'Australia', '58824');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Singapore', 'Asia', '62690');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Denmark', 'Europe', '63434');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('United States of America', 'North America', '64906');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Qatar', 'Asia', '65062');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Iceland', 'Europe', '78181');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Ireland', 'Europe', '81477');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Swirzerland', 'Europe', '83832');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Norway', 'Europe', '86362');
INSERT INTO richest_countries (country, continent, gdp_per_capita) VALUES ('Luxembourg', 'Europe', '119719');

Screenshot of a Postgres create database SQL statement for a Postgres ORDER BY DESC example

Postgres sort by statement using ‘ORDER BY’

In the following example, we will display the richest countries and sort them by their GDP per capita, returning the records in descending order:

1
2
3
SELECT country, continent, CAST(gdp_per_capita AS MONEY)
FROM richest_countries
ORDER BY gdp_per_capita DESC;

Notice that the above SQL statement uses CAST in conjunction with AS MONEY: This returns the gdp_per_capita column values in a USD currency format. The query then sorts the Postgres records from the highest GDP per capita to the least:

1
2
3
4
5
6
7
8
9
10
11
12
13
         country          |   continent   | gdp_per_capita
--------------------------+---------------+----------------
 Luxembourg               | Europe        |    $119,719.00
 Norway                   | Europe        |     $86,362.00
 Swirzerland              | Europe        |     $83,832.00
 Ireland                  | Europe        |     $81,477.00
 Iceland                  | Europe        |     $78,181.00
 Qatar                    | Asia          |     $65,062.00
 United States OF America | North America |     $64,906.00
 Denmark                  | Europe        |     $63,434.00
 Singapore                | Asia          |     $62,690.00
 Australia                | Australia     |     $58,824.00
(10 ROWS)

Sorting records in descending order with limit

In our next example, we’ll sort the richest countries, but this time we’ll limit the number of records that will be returned:

1
2
3
4
SELECT country, continent, CAST(gdp_per_capita AS MONEY)
FROM richest_countries
ORDER BY gdp_per_capita DESC
LIMIT 5;

This statement should return the following:

1
2
3
4
5
6
7
8
   country   | continent | gdp_per_capita
-------------+-----------+----------------
 Luxembourg  | Europe    |    $119,719.00
 Norway      | Europe    |     $86,362.00
 Swirzerland | Europe    |     $83,832.00
 Ireland     | Europe    |     $81,477.00
 Iceland     | Europe    |     $78,181.00
(5 ROWS)

Screenshot of a PostgreSQL cast or Postgres cast example using the Postgres ORDER BY DESC clause

Sort by PostgreSQL statement using several columns

When you use the ORDER BY keywords, you can sort records on more than one column. For this example, we’ll sort the richest countries by continent and then sort by GDP per capita in descending order:

1
2
3
SELECT country, continent, CAST(gdp_per_capita AS MONEY)
FROM richest_countries
ORDER BY continent, gdp_per_capita DESC;

The above SQL statement will return the following table:

1
2
3
4
5
6
7
8
9
10
11
12
13
         country          |   continent   | gdp_per_capita
--------------------------+---------------+----------------
 Qatar                    | Asia          |     $65,062.00
 Singapore                | Asia          |     $62,690.00
 Australia                | Australia     |     $58,824.00
 Luxembourg               | Europe        |    $119,719.00
 Norway                   | Europe        |     $86,362.00
 Swirzerland              | Europe        |     $83,832.00
 Ireland                  | Europe        |     $81,477.00
 Iceland                  | Europe        |     $78,181.00
 Denmark                  | Europe        |     $63,434.00
 United States OF America | North America |     $64,906.00
(10 ROWS)

Conclusion

When you execute a PostgreSQL query, being able to sort the returned records makes your results more readable and helps you gain better insights from your data. The ORDER BY keywords allow you to specify a column on which to sort your results; adding the DESC clause makes it easy to sort in descending order. In this article, we explained how to use the Postgres ORDER BY DESC keywords to sort the results of a query. With our examples to get you started, you’ll be able to utilize these keywords 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.