Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Help with sorting results

    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).

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, see next post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2009
    Posts
    2
    r937 thanks a lot it worked. Really appreciate your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •