Spencer Ollila Spencer Ollila - 2 months ago 10
MySQL Question

Pulling forum threads in order of latest post

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.

Answer

You can do that in one request by following example

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;

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.