Select all threads and order by the latest one

Question

Now that I got the Select all forums and get latest post too.. how? question answered, I am trying to write a query to select all threads in one particular forum and order them by the date of the latest post (column "updated_at").

This is my structure again:

forums                      forum_threads              forum_posts
----------                  -------------             -----------
id                          id                        id
parent_forum (NULLABLE)     forum_id                  content
name                        user_id                   thread_id
description                 title                     user_id
icon                        views                     updated_at
                            created_at                created_at
                            updated_at
                            last_post_id (NULLABLE)

I tried writing this query, and it works.. but not as expected: It doesn't order the threads by their last post date:

SELECT DISTINCT ON(t.id) t.id, u.username, p.updated_at, t.title
FROM   forum_threads             t
LEFT   JOIN forum_posts   p ON p.thread_id = t.id
LEFT   JOIN users         u ON u.id = p.user_id
WHERE t.forum_id = 3
ORDER  BY t.id, p.updated_at DESC;

How can I solve this one?

Answer 1

Assuming you want a single row per thread and not all rows for all posts.

DISTINCT ON is still the most convenient tool. But the leading ORDER BY items have to match the expressions of the DISTINCT ON clause. If you want to order the result some other way, you need to wrap it into a subquery and add another ORDER BY to the outer query:

SELECT *
FROM  (
   SELECT DISTINCT ON (t.id)
          t.id, u.username, p.updated_at, t.title
   FROM   forum_threads      t
   LEFT   JOIN forum_posts   p ON p.thread_id = t.id
   LEFT   JOIN users         u ON u.id = p.user_id
   WHERE  t.forum_id = 3
   ORDER  BY t.id, p.updated_at DESC
   ) sub
ORDER  BY updated_at DESC;

If you are looking for a query without subquery for some unknown reason, this should work, too:

SELECT DISTINCT
       t.id
     , first_value(u.username)   OVER w AS username
     , first_value(p.updated_at) OVER w AS updated_at
     , t.title
FROM   forum_threads      t
LEFT   JOIN forum_posts   p ON p.thread_id = t.id
LEFT   JOIN users         u ON u.id = p.user_id
WHERE  t.forum_id = 3
WINDOW w AS (PARTITION BY t.id ORDER BY p.updated_at DESC)
ORDER  BY updated_at DESC;

There is quite a bit going on here:

1. The tables are joined and rows are selected according to JOIN and WHERE clauses.

2. The two instances of the window function first_value() are run (on the same window definition) to retrieve username and updated_at from the latest post per thread. This results in as many identical rows as there are posts in the thread.

3. The DISTINCT step is executed after the window functions and reduces each set to a single instance.

4. ORDER BY is applied last and updated_at references the OUT column (SELECT list), not one of the two IN columns (FROM list) of the same name.

Yet another variant, a subquery with the window function row_number():

SELECT id, username, updated_at, title
FROM  (
   SELECT t.id
        , u.username
        , p.updated_at
        , t.title
        , row_number() OVER (PARTITION BY t.id
                             ORDER BY p.updated_at DESC) AS rn
   FROM   forum_threads      t
   LEFT   JOIN forum_posts   p ON p.thread_id = t.id
   LEFT   JOIN users         u ON u.id = p.user_id
   WHERE  t.forum_id = 3
   ) sub
WHERE  rn = 1
ORDER  BY updated_at DESC;

Similar case:

• Return records distinct on one column but order by another column

You'll have to test which is faster. Depends on a couple of circumstances.

Answer 2

Forget the distinct on:

SELECT t.id, u.username, p.updated_at, t.title
FROM   forum_threads             t
LEFT   JOIN forum_posts   p ON p.thread_id = t.id
LEFT   JOIN users         u ON u.id = p.user_id
WHERE t.forum_id = 3
ORDER  BY p.updated_at DESC;