The PostgreSQL Select Most Recent Record by Date

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

Introduction

In PostgreSQL, the SELECT statement is used to retrieve records from the database. You can use a WHERE clause in your query to retrieve only the records that match certain conditions. What if you wanted to retrieve just the most recent record by the date? There are a few simple methods we can use to accomplish this task. In this article, we’ll show you how to use SQL to select the most recent record by date.

Prerequisites

Throughout this tutorial, we’ll be looking at different examples of how to use SQL to select the most recent record by date. If you want to follow along with these examples, you’ll need to have the following prerequisites in place:

  • Make sure you have PostgreSQL installed on your computer so you can test out the examples using SQL to select most recent record by date.

  • You need to have at least some basic knowledge of PostgreSQL.

Create a table and database in PostgreSQL

Let’s begin by creating a database and tables to use in our queries. We can use the psql interactive terminal to perform this task.

To create a database in PostgreSQL, we can use a statement like the one shown below:

1
CREATE DATABASE db_name;

After creating the database, we’ll use the command \c followed by mydb to enter into the new database.

We can use the following statement to create a table in PostgreSQL:

1
2
3
4
5
6
CREATE TABLE employees(
id SERIAL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
gender VARCHAR(30) NOT NULL,
employment_date DATE NOT NULL
);

We’ll also insert some records into our table:

1
2
3
4
5
6
7
8
INSERT INTO employees (name, gender, employment_date)
VALUES ('Erick Seward','Male','2019-05-19'),
('Olivia Lawson','Female','2019-06-18'),
('Edison Rowland','Male','2017-03-19'),
('Willard Anderson','Male','2018-06-02'),
('Eleanor Harmon','Female','2019-12-12'),
('Jade Bowman','Female','2019-10-29'),
('Bradley White','Male','2018-03-13');

At this point, our table is now ready for use. The contents of the table will look like this:

1
2
3
4
5
6
7
8
9
10
 id |       name       | gender | employment_date
----+------------------+--------+-----------------
  1 | Erick Seward     | Male   | 2019-05-19
  2 | Olivia Lawson    | Female | 2019-06-18
  3 | Edison Rowland   | Male   | 2017-03-19
  4 | Willard Anderson | Male   | 2018-06-02
  5 | Eleanor Harmon   | Female | 2019-12-12
  6 | Jade Bowman      | Female | 2019-10-29
  7 | Bradley White    | Male   | 2018-03-13
(7 ROWS)

Use the SELECT query and ORDER BY date

In the following example, we’ll display the list of all employees in the table and sort them by the date of their employment:

1
SELECT * FROM employees ORDER BY employment_date DESC;

This query will return a result like this:

1
2
3
4
5
6
7
8
9
10
 id |       name       | gender | employment_date
----+------------------+--------+-----------------
  5 | Eleanor Harmon   | Female | 2019-12-12
  6 | Jade Bowman      | Female | 2019-10-29
  2 | Olivia Lawson    | Female | 2019-06-18
  1 | Erick Seward     | Male   | 2019-05-19
  4 | Willard Anderson | Male   | 2018-06-02
  7 | Bradley White    | Male   | 2018-03-13
  3 | Edison Rowland   | Male   | 2017-03-19
(7 ROWS)

Using ORDER BY and LIMIT clause in PostgreSQL

In this section, we’ll go over the different ways to get the most recent record by date.

Let’s select the most recent employee that has been employed using the ORDER BY clause and specifying a LIMIT of ‘1’:

1
2
3
4
5
SELECT * FROM employees ORDER BY employment_date DESC LIMIT 1;
 id |      name      | gender | employment_date
----+----------------+--------+-----------------
  5 | Eleanor Harmon | Female | 2019-12-12
(1 ROW)

Using ORDER BY and FETCH clause in PostgreSQL

Next, let’s try a different way of selecting the most recent employee that has been employed. This time, we’ll use the ORDER BY clause and the FETCH clause to get the information we need:

1
2
3
4
5
SELECT * FROM employees ORDER BY employment_date DESC FETCH FIRST ROW ONLY;
 id |      name      | gender | employment_date
----+----------------+--------+-----------------
  5 | Eleanor Harmon | Female | 2019-12-12
(1 ROW)

We can see that both methods return the same correct result.

Using the max function in PostgreSQL

Yet another way to accomplish the same task is to use the MAX() function. We can use this function to select the most recent employee that has been employed:

1
2
3
4
5
SELECT * FROM employees WHERE employment_date = (SELECT MAX(employment_date) FROM employees);
 id |      name      | gender | employment_date
----+----------------+--------+-----------------
  5 | Eleanor Harmon | Female | 2019-12-12
(1 ROW)

Once again, we get the same result.

Conclusion

If you need to select just the most recent record in a table by date, you may wonder how to construct a query to get this information. In this article, we showed you a few different ways to use SQL to select the most recent record by date. With these examples to guide you, you’ll be able to construct similar queries in your own PostgreSQL environment.

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.