Postgres ORDER BY DESC
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'); |
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) |
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