Rank function explained - PostgreSQL

Introduction

Let's consider the following case: you have posts table where you store all articles that are presented on your blog and you have another table called comments where you store comments added to the given post. How can you pull all posts along with the most recent comment added to it? The answer is rank function. Let's take a closer look at it.

Test data creation

Before I begin to explain the rank function, I need to demonstrate the way it is working on a test data set. Let's create then posts and comments table.

Tables creation

The posts table will be really simple and it will consist only of two fields: id and title. The id column will be unique and auto-incremented which is a quite common scheme:

CREATE TABLE posts (
  id     SERIAL PRIMARY KEY,
  title  VARCHAR(100) NOT NULL
);

The comments table will cover the id , body, post_id and created_at column. The post_id will be a reference to the posts table and will allow us to connect given comments with the given post and the created_at column will hold the comment's creation time which will allow us to pull only recent comments.

Let's create it with the following query:

CREATE TABLE comments (
  id         SERIAL PRIMARY KEY,
  body       VARCHAR(100) NOT NULL,
  post_id    INT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

Test data insertion

Tables are ready so we can insert some test data into them to demonstrate how the rank function works and why it is so useful. Let's start with the posts table:

INSERT INTO public.posts (title)
VALUES  ('SQL tutorial'),
        ('Ruby tutorial'),
        ('JavaScript tutorial')

and the comments table:

INSERT INTO public.comments (body, post_id, created_at)
VALUES  ('Nice job!', 1, timestamp '2020-07-05 04:05:06'),
        ('Very interesting', 1, timestamp '2020-07-05 03:05:06'),
        ('I do not understand anything', 1, timestamp '2020-07-05 02:05:06'),
        ('Cool, I did not know about it', 2, timestamp '2020-07-05 04:05:06'),
        ('Looking forward for the next part', 2, timestamp '2020-07-05 04:06:06'),
        ('Just wanted to say thank you', 3, timestamp '2020-07-05 03:05:06'),
        ('Hello world', 3, timestamp '2020-07-05 04:01:06'),
        ('Great!', 3, timestamp '2020-07-05 02:05:06')

We have now the complete data to begin the demonstration.

Rank function explanation

Let's take a look at the official documentation to see the format of the rank function:

SELECT columns
FROM
  (SELECT columns,
          rank() OVER (PARTITION BY partition_column ORDER BY order_column DESC) AS pos
     FROM table_name
  ) AS sub_select
WHERE pos < 3;

Definition explanation

  • columns - the list of columns from the given table that you want to receive in the result
  • partition_column - the column that is going to be used to divide the results into groups. In our case, it will be the post_id because we want to pull the most recent comment for each post so we have to create a one group per one post
  • order_column - the column that is going to be used for sorting. In our case, it will be the created_at from the comments table because we want to give the highest rank number to the comment that was created last
  • pos - position value of the given record from the query. This value will help us to pull the desired number of records or records that match only given criteria

Creating query for our test data

We have the test data inserted and the query definition explained to the most important step now is to create the query that will return each post record along with the newest comment that was added to it:

SELECT id, title, body FROM (
      SELECT posts.id as id,
             posts.title as title,
             comments.body as body,
             comments.created_at as created_at,
             rank() OVER (
               PARTITION BY posts.id
               ORDER BY created_at DESC
             ) AS comment_rank
      FROM posts LEFT JOIN comments ON comments.post_id = posts.id
    ) AS ranked_posts WHERE comment_rank = 1

We will now receive the following results:

1, SQL tutorial, Nice job!
2, Ruby tutorial, Looking forward for the next part
3, JavaScript tutorial, Hello world
Top