Select All Threads and Order by the Latest One

Select all threads and order by the latest one

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.

SQL query to select all forum threads but order them by the latest active thread

Add a column containing the last time a thread has been replied into your threads table, and then use order by this col. Don't forget to update the column on new reply.

There are ways to do it manually, but it will be far more slower than a dedicated column.

MySQL: Select threads and posts, order by latest activity

SELECT t.*, IFNULL(MAX(p.`timestamp`), t.`timestamp`) AS pts FROM `threads` t, `posts` p WHERE p.`thread_id` = t.`id` AND t.`forum_id`=1 ORDER BY pts DESC

SQL: Select latest thread and latest post, grouped by forum, ordered by latest post

Since MySQL doesn't support window functions, I don't think there's any way to do this without a subquery:

SELECT  f.id AS forum_id,
    f.name AS forum_name,
    t.id AS thread_id,
    t.topic AS thread_topic,
    t.ts AS thread_timestamp,
    p.id AS post_id,
    p.content AS post_content,
    p.ts AS post_timestamp

FROM   forums f
JOIN (SELECT t2.forum_id, max(p2.ts) as ts
      FROM posts p2
      JOIN threads t2 ON p2.thread_id = t2.id
      GROUP BY t2.forum_id) max_p ON f.id = max_p.forum_id
JOIN   posts p ON max_p.ts = p.ts
JOIN   threads t ON f.id = t.forum_id AND p.thread_id = t.id
ORDER BY p.ts

Naturally, caching the latest results would let you do this without the performance penalty of calling MAX(), but with the right indices, this shouldn't be much of an issue...

UPDATE

The most concise way of including the threads without posts and forums without threads would be to use LEFT JOINs instead of an INNER JOINs:

SELECT  f.id AS forum_id,
    f.name AS forum_name,
    t.id AS thread_id,
    t.topic AS thread_topic,
    t.ts AS thread_timestamp,
    p.id AS post_id,
    p.content AS post_content,
    p.ts AS post_timestamp

FROM   forums f
LEFT JOIN (SELECT t2.forum_id, max(COALESCE(p2.ts, t2.ts)) as ts, COUNT(p2.ts) as post_count
      FROM threads t2 
      LEFT JOIN posts p2 ON p2.thread_id = t2.id
      GROUP BY t2.forum_id) max_p ON f.id = max_p.forum_id
LEFT JOIN   posts p ON max_p.ts = p.ts
LEFT JOIN   threads t ON f.id = t.forum_id AND (max_p.post_count = 0 OR p.thread_id = t.id)
ORDER BY p.ts

MySql SELECT only newest message from distinct threads order by timestamp -Private Messages Inbox Similar to Facebooks(2013)

    SELECT * FROM 
        (SELECT * FROM messages ORDER BY created_at DESC) messages
    GROUP BY IF(from_user_id < to_user_id, CONCAT_WS('_', from_user_id, to_user_id), CONCAT_WS('_', to_user_id, from_user_id));

Selecting latest message per thread

Try this;)

select t1.*
from messages t1
inner join (
    select max(date_posted) as date_posted, thread_id
    from messages
    group by thread_id
) t2 on t2.thread_id = t1.thread_id and t2.date_posted = t1.date_posted
order by t1.date_posted

Or you can use in :

select *
from messages
where (date_posted, thread_id) in (
    select max(date_posted) as date_posted, thread_id
    from messages
    group by thread_id
)
order by date_posted

SQLFiddle DEMO HERE

Select all threads with tag & the rest of the tags?

Something like

SELECT threads.*, GROUP_CONCAT(tags.title)
FROM threads AS t
LEFT JOIN thread_tags AS tt ON t.id = tt.threads_id
LEFT JOIN tags AS tt ON tt.tag_id = tags.id
WHERE t.id IN (
    SELECT tt.threads_id
    FROM thread_tags AS tt
    JOIN tags ON tt.tag_id = tags.id 
             AND tags.title = "test"
)
GROUP BY t.id

---

Related Topics