SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Apr 2004
    Location
    Est
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to order threads by last posting time?

    I made little board, where all postings are in one table:
    Code:
    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 is, if 0 then its new thread else reply for exist one.

    Now i want to sort threads by last posting_time of replyes. Is it possible to do this without sub query and if yes then how?

  2. #2
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I understand you correctly it would be:
    SELECT topic_id, MAX(posting_time) AS last_post FROM table WHERE topic_pid<>0 GROUP BY topic_id ORDER BY last_post DESC

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess not.
    If you have an additional field 'last_reply_time' or better you have 2 tables, one for threads and one for replies, it becomes easy.

    --
    Mario
    Cold!Scripts.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    actually, the sql for 2 tables (threads and replies) is messier than the sql for only one (threads and replies combined)
    Code:
    select topic_id
         , topic_pid
         , author
         , mail
         , posting_time
         , ip
         , body
         , reg_user
      from postings
    order
        by posting_time desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    actually, the sql for 2 tables (threads and replies) is messier than the sql for only one (threads and replies combined)
    Code:
    select topic_id
         , topic_pid
         , author
         , mail
         , posting_time
         , ip
         , body
         , reg_user
      from postings
    order
        by posting_time desc
    It's not clear if your code is for 1 table or 2. From the table name and fields I guess it is for the unique table solution.

    OP however wants to sort threads by the time they were last replied to, not simply sorting all posts. With his current table it's not possible to do so in a single plain query.

    --
    Mario
    Cold!Scripts.com - with screenshots

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ColdScripts.com
    It's not clear if your code is for 1 table or 2. From the table name and fields I guess it is for the unique table solution.
    good guess



    Quote Originally Posted by ColdScripts.com
    OP however wants to sort threads by the time they were last replied to, not simply sorting all posts. With his current table it's not possible to do so in a single plain query.
    you're right, i misunderstood, and you're right, it takes a join

    Code:
    select threads.topic_id
         , threads.topic_pid
         , left(body,250)   as excerpt
         , coalesce(
              max(replies.posting_time) 
                 ,threads.posting_time
                   )        as last_reply_time
      from postings as threads
    inner
      join postings as replies  
        on threads.topic_id
         = replies.topic_pid
     where threads.topic_pid = 0     
    group
        by threads.topic_id
         , threads.topic_pid   
         , excerpt
    order
        by last_reply_time desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Apr 2004
    Location
    Est
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I went already simpler way and added new field for last posting as suggested ColdScripts.com.

    Thanks.


Bookmarks

Posting Permissions

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