Pulling forum threads in order of latest post

Question

So, I have one table, Threads

ThreadNumber | Sticky | Title  
1            | 1      | Read This Before Posting!  
2            | 0      | Second Topic  
3            | 0      | Check This

And a second table, Posts

ThreadNumber | Author | Timestamp | Body                        | PostNumber  
1            | User1  | 1         | Read up!                    | 0  
1            | User2  | 2         | I see.                      | 1  
2            | User2  | 3         | So tell me what'chu want!   | 0  
3            | User3  | 5         | Yeah, check this out.       | 0  
2            | User3  | 7         | What'chu really really want!| 1  
2            | User1  | 10        | I'll tell you what I want!  | 2

(Timestamp in Posts is the ten-digit second integer representation of time that the PHP time() function gives out when the given user submits their comment to the db.) So, when it comes to showing all forum thread topics on the forum overview page, what I would like to do is present all of the threads in order of when their last comment was posted. I have tried my own hand at nested MySQL queries (didn't work out), so I started trying to do some research. Nothing else that I found was able to get me what I was looking for either. I feel like this came close, but I still got all the results given by unique ThreadNumbers. Ideally, what I would get returned is something like:

ThreadNumber | Title                     | LastTimestamp  
2            | Second Topic              | 10  
3            | Check This                | 5  
1            | Read This Before Posting! | 1

Thanks a ton for reading this and any possible [and much needed] help!

I have tried

    SELECT PostNumber, ThreadNumber, Body, Author, Timestamp
FROM (SELECT PostNumber, ThreadNumber, Body, Author, Timestamp
      FROM Posts
      ORDER BY Timestamp DESC) AS p
GROUP BY ThreadNumber;

which not only wouldn't have returned what I actually wanted, but didn't work the way I intended anyways.

Can you update your question with the query that you have tried so far ? — Maximus2012, Sep 07 '16 at 20:37

Spell · Accepted Answer · 2016-09-08T06:23:56.610

You can do that in one request by following example

select 
  Posts.ThreadNumber, 
  Title, 
  max(Timestamp) 
from Threads 
left join Posts on Threads.ThreadNumber = Posts.ThreadNumber  
group by Posts.ThreadNumber, Title 
order by max(timestamp) desc;

With source data like

mysql> select * from Posts;
+--------------+--------+-----------+------+------------+
| ThreadNumber | Author | Timestamp | Body | PostNumber |
+--------------+--------+-----------+------+------------+
|            1 | User1  |        20 |      |          0 |
|            2 | User1  |        30 |      |          0 |
|            3 | User1  |        10 |      |          0 |
|            1 | User1  |        24 |      |          0 |
|            2 | User1  |         2 |      |          0 |
|            3 | User1  |       200 |      |          0 |
+--------------+--------+-----------+------+------------+
6 rows in set (0,00 sec)

and

mysql> select * from Threads;
+--------------+--------+----------+
| ThreadNumber | Sticky | Title    |
+--------------+--------+----------+
|            1 |      0 | Thread 1 |
|            2 |      0 | Thread 2 |
|            3 |      0 | Thread 3 |
+--------------+--------+----------+
3 rows in set (0,00 sec)

will output you following

mysql> select distinct Posts.ThreadNumber, Title, max(Timestamp) from Threads left join Posts on Threads.ThreadNumber = Posts.ThreadNumber  group by Posts.ThreadNumber, Title order by max(timestamp) desc;
+--------------+----------+----------------+
| ThreadNumber | Title    | max(Timestamp) |
+--------------+----------+----------------+
|            3 | Thread 3 |            200 |
|            2 | Thread 2 |             30 |
|            1 | Thread 1 |             24 |
+--------------+----------+----------------+
3 rows in set (0,00 sec)

But in your case I will redevelop database structure. You have some very bad issues in your structure. At least you may add id field in Posts. For now you cannot set valid index in it and your requests will be slower with big data.

Why do you need `distinct` here? – zerkms Sep 08 '16 at 02:48 — zerkms, Sep 08 '16 at 02:48
Yeah, actually it does not have any sense here. – Spell Sep 08 '16 at 06:23 — Spell, Sep 08 '16 at 06:23

Pulling forum threads in order of latest post

1 Answers1