SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Aug 2002
    Location
    St. Louis, MO
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Funky ORDER BY question

    I have two tables, one consisting of postings, another consisting of replies. Some postings may not have replies. Here is the basic idea of my structure:

    Code:
    tblPostings
    -----------
    PostingID
    PostingDate
    Subject
    Message
    
    
    tblReplies
    ----------
    ReplyID
    PostingID
    PostingDate
    Subject
    Message

    Pretty straight forward. So, to display a list of all postings, I want to sort them by PostingDate of the most recent reply. If there is no reply, I want to sort by the posting date. When I try using a JOIN like this:

    Code:
    SELECT p.ForumMessageID, p.Subject FROM tblPostings AS p 
    INNER JOIN tblReplies AS r ON r.PostingID = m.PostingID 
    ORDER BY r.PostingDate DESC
    it ignores all postings without replies. Does anybody have any ideas about how I could accomplish this??

    Thanks in advance!

    -GodOfHonk

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try the following (yes, i tested it):
    Code:
    select p.Subject, p.Message, p.PostingDate 
         , r.Subject, r.Message, r.PostingDate 
      from tblPostings p 
    left outer
      join tblReplies  r 
        on p.PostingID = r.PostingID 
    order
        by coalesce(r.PostingDate,p.PostingDate) desc
    the left outer join returns all postings, with or without replies

    the coalesce in the ORDER BY will test the reply's posting date, and if it's null (i.e. there is no reply for this posting), it uses the posting date instead

    rudy
    http://rudy.ca/

  3. #3
    SitePoint Member
    Join Date
    Aug 2002
    Location
    St. Louis, MO
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! I think I'm almost there. This works except that if there is more than one reply, the query generates a record for each reply. I need it to determine wether to use the most recent reply date, or the message posting date if there are no replies. I played around with DISTINCT but couldn't get it right.

    I realize I may be asking the impossible.

    Any ideas?

    -GodOfHonk

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    >> "if there is more than one reply, the query
    >> generates a record for each reply"

    okay, i get it now, sorry

    here's just the postings, along with their latest reply date, if any

    Code:
    select p.PostingID, p.Subject
         , coalesce(max(r.PostingDate),p.PostingDate)
      from tblPostings p 
    left outer
      join tblReplies  r 
        on p.PostingID = r.PostingID 
    group
        by p.PostingID
    order
        by coalesce(max(r.PostingDate),p.PostingDate) desc

  5. #5
    SitePoint Member
    Join Date
    Aug 2002
    Location
    St. Louis, MO
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm.. almost. I'm getting "Invalid use of group function" as a error back from MySQL


    Any Ideas?

    btw.. I really really appreciate your help! You're saving my butt.

    -GodOfHonk

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i can't believe i wrote that

    it should be
    Code:
    group
        by p.PostingID, p.Subject
         , p.PostingDate

  7. #7
    SitePoint Member
    Join Date
    Aug 2002
    Location
    St. Louis, MO
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm... Still getting "Invalid use of group function". I changed some field and table names, but that's all. Here's the exact query I'm using:

    Code:
    select p.ForumMessageID, p.Subject
         , coalesce(max(r.PostingDate),p.PostingDate)
      from tblForumMessages p 
    left outer
      join tblForumReplies  r 
        on p.ForumMessageID = r.ForumMessageID 
    group
        by p.ForumMessageID, p.Subject
         , p.PostingDate
    order
        by coalesce(max(r.PostingDate),p.PostingDate) desc LIMIT 0, 30
    Thanks again!!

    -GodOfHonk

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    weird

    well, this works:
    Code:
    select p.PostingID, p.Subject
         , max(
            case 
              when r.PostingID is null
              then p.PostingDate
              else r.PostingDate
            end ) as LastEntry
      from tblPostings p 
    left outer
      join tblReplies  r 
        on p.PostingID = r.PostingID
    group
        by p.PostingID, p.Subject
    order
        by LastEntry desc

  9. #9
    SitePoint Member
    Join Date
    Aug 2002
    Location
    St. Louis, MO
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BINGO!

    Got it! Works like a charm, thanks a bunch!!!!

    -GodOfHonk


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
  •