How to Execute a Psql Sort by Date Query
When you perform a query in PostgreSQL, you may want your results to come back in a particular order. Sorting by date is one of the most common ways to order results. In this article, we’ll explain how to perform a psql sort by date query, using the
ORDER BY clause to specify our chosen sort order in the
Before you begin this tutorial, make sure that PostgreSQL is installed and configured on your machine. You should also have some working knowledge of PostgreSQL in order to follow along with the examples in this article.
Access the Command-line Interface
To execute queries in
psql, you’ll need to log in as the ‘postgres’ superuser. You would then use the command
psql to connect to the command-line console.
First, you’ll enter the command shown below:
sudo su - postgres
You’ll be prompted for a password. Enter it and then use the command
psql to begin querying data in the PostgreSQL database.
PostgreSQL Sort by Date
To perform a query that’s sorted by date, you use the
ORDER BY clause in your
SELECT statement. This clause allows you to sort rows in ascending or descending order based on the specified criteria.
By default, PostgreSQL queries are returned in an unspecified order. Therefore, you can control how the results are sorted by using
Creating a Database in PostgreSQL
Let’s begin by creating a PostgreSQL database. Later, we’ll create a table within this database with sample data that we can use in our examples.
To create a new database, we can use the following command:
CREATE DATABASE billings;
If you’d like to see a list of all databases, use the command
To connect to your new database, enter
\c followed by the database name. You’ll get a response from PostgreSQL that you are now connected to the database.
Creating a Table in PostgreSQL
To create a new table, we use the
CREATE TABLE statement. The basic syntax of this statement is shown below:
CREATE TABLE TABLE_NAME( COL NAME +
DATA TYPE + COL_CONSTRAINT);
Here’s the statement we’ll use to create our sample table:
CREATE TABLE collection(
Notice that the table name always comes after the
CREATE TABLE statement. After that comes the list of column names, their data types and any optional constraints.
Inserting Data into the PostgreSQL Table
Now that we created a table, let’s use an
INSERT INTO statement to add some records to our table:
INSERT INTO TABLE_NAME(col_1, col_2 . . . ,) VALUES
(value_1,value_2. . . ,);
NOTE: When you list values to insert, they should be in the same order as their respective column names.
name | billing_date | due_date | collection_date
sally | 2020-01-02 | 2020-01-05 | 2020-01-10
jeremiah | 2020-01-03 | 2020-01-06 | 2020-01-20
ensim | 2020-01-01 | 2020-01-09 | 2020-01-06
joven | 2020-01-05 | 2020-01-12 | 2020-01-07
You can see the unspecified order of dates in the example above.
Sorting the Date in a PostgreSQL Table
In PostgreSQL, sorting by date requires the
ORDER BY clause, which is used in conjunction with a
The syntax for
ORDER BY clause is:
SELECT col_1,col_2...,FROM TABLE_NAME
ORDER BY col_1 [ASC | DESC],
col_2 [ASC | DESC]...,
Sorting the Date in Descending Order
Now that we understand how the
ORDER BY clause works to oontrol sorting, let’s look at an example where we sort the
billing_date in descending order:
ORDER BY billing_date DESC;
The result of this query would be:
Sorting the Date in Ascending Order
In PostgreSQL, it’s possible to use the
ORDER BY clause to sort the date in either ascending or descending order. When you use the
ORDER BY clause, ascending order is used as the default when no
DESC modifier is specified.
Let’s look at an example:
SELECT due_date FROM collection ORDER BY due_date ASC;
This example should return records sorted by the
due_date field in ascending order.
The result of this query would be:
NOTE: You can omit the
ASC attribute from your
ORDER BY clause when you sort in ascending order since the ascending attribute is set as default.
Sorting by Date in Multiple Columns
You can even use the
ORDER BY clause to sort on multiple columns in PostgreSQL.
Let’s say we want to sort by _duedate in ascending order but sort by _collectiondate in descending order. We’d use the
SELECT statement shown below:
collection ORDER BY due_date ASC,
The output will look like this:
due_date | collection_date
2020-01-05 | 2020-01-10
2020-01-06 | 2020-01-20
2020-01-09 | 2020-01-06
2020-01-12 | 2020-01-07
It’s clear that both sort orders specified in the
ORDER BY clause are apparent in the query results.
Sorting Date using WHERE Clause in PostgreSQL
In PostgreSQL, you can also sort data using a
WHERE clause in your
SELECT statement. This will allow you to filter out rows that you don’t want included in your results.
The syntax for this type of query is shown below:
SELECT column_name FROM TABLE
[WHERE conditions]ORDER BY column_name;
Here’s an example of how it works:
SELECT billing_date FROM collection
WHERE name = 'joven'
ORDER BY billing_date ASC;
For this example, the output would be:
Notice that the
ORDER BY clause returned the records sorted by the billing date field in ascending order, and the
WHERE clause limited results to those whose
name field was equal to ‘joven’ in the
If you need your query results to be returned in a certain order, the
ORDER BY clause provides a simple way to get the job done. In this article, we showed you how to perform a psql sort by date query, adding the
ORDER BY clause to a
SELECT statement to specify a particular sort order. With our examples to guide you, you’ll be able to create your own PostgreSQL queries that are sorted by date.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started