Help with sorting results

JackBauer3 · #1 (**permalink**) 12-10-09, 16:03

Hi I need help with sql query to get results in correct order

I've two tables:
Topic [ idtopic, idforum, title, description, views]
Post [ idpost, idtopic, iduser, message, dateadded ]

I need to get data from Topic table that is sorted by post.dateadded. It's for topics listing page on forum so it works like: somebody adds reply to topic and in my query fetching all topics that row should go on top. I hope you understand what i mean.

This is what i managed to write

Code:

SELECT t.title, p.dateadded
FROM Topic AS t
JOIN Post AS p ON p.idtopic = t.idtopic
WHERE t.idforum =1
ORDER BY p.dateadded DESC

That is almost what i want but I need it to sort by max(p.dateadded) -> the last post from topic, not by all posts like it's now (There should be less results).

r937 · #2 (**permalink**) 12-10-09, 16:22

oops, see next post

r937 · #3 (**permalink**) 12-10-09, 16:23

Code:

SELECT t.title
     , p.last_post
  FROM Topic AS t
INNER
  JOIN ( SELECT idtopic
              , MAX(dateadded) AS last_post
           FROM Post 
         GROUP
             BY idtopic ) AS p 
    ON p.idtopic = t.idtopic
 WHERE t.idforum =1
ORDER 
    BY p.last_post DESC

JackBauer3 · #4 (**permalink**) 12-10-09, 16:43

r937 thanks a lot it worked. Really appreciate your help!

Internet Services

Web Development

Digital Marketing

Consumer Tech