[EMAIL PROTECTED] wrote:
Hello,
I made little board, where all postings are in one table:
Field Type ------------ --------------- topic_id int(6) unsigned topic_pid int(6) unsigned author varchar(50) mail varchar(255) posting_time timestamp(14) ip varchar(15) body text reg_user tinyint(1)
topic_pid shows what kind of topic it is, if 0 then its new thread else reply for exist one.
Now i want to sort threads by last posting_time but because i havent mysql 4.1 in server then i cant use sub query. Is it possible to get it with joins and if yes then how?
-- Lauri
You need to break it down into two steps: First get the latest posting times, holding the result in a temporary table, then get all the posts properly ordered. Something like this (I'm using 'board' as the name of your table):
CREATE TEMPORARY TABLE pt (
topic_pid int unsigned,
latest timestamp
index (latest, topic_pid));LOCK TABLES board READ;
INSERT INTO pt
SELECT topic_pid, MAX(posting_time) AS latest
FROM board
GROUP BY topic_pid;SELECT board.topic_id, board.topic_pid, board.author, board.posting_time FROM pt JOIN board USING (topic_pid) ORDER BY pt.latest DESC, pt.topic_pid DESC, board.posting_time DESC;
UNLOCK TABLES; DROP TABLE tmp;
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
