SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to select 3 topic and list the most recent comment

    I been banging my head against the wall. I'm not entirely sure what i am doing wrong here...

    I hope some SQL guru is out there and willing to help.

    I have two tables. Discussion_Topic and Discussion_Comment.
    There is a dtop_id value in the 'discussion_topic' table that matches a column in 'discussion_comment' called 'dcom_topic_id'. (that allows for selecting comments that belong to the topic with matching IDs. Pretty simple.)

    I made a new column in discussion_topic called 'rank'. There will be 3max listed and they are labeled 1,2,3 and in that order too. So i can grab 3 topic titles and output them to the page. (not having a problem with this)

    Next I want to grab the most recent comment from the comments table and output the comment just below each topic title. And Here lies the issue. The results always show the oldest comment.

    Here is the SQL statement i am using:

    Code:
    SELECT * FROM discussion_topic, discussion_comment
    WHERE discussion_topic.dtop_id = discussion_comment.dcom_topic_id
    AND discussion_topic.rank > 0
    AND discussion_topic.dtop_active = 1
    GROUP BY discussion_topic.dtop_id
    ORDER BY discussion_topic.jtcrank ASC
    I think this is probably just something simple that i am doing wrong.

    thanks in advance!
    Last edited by RobbieGoD; Jan 27, 2010 at 14:48. Reason: wanted to add more info to make it more clear.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Don't know if it works, but you could try
    Code:
    ORDER BY 
        discussion_topic.jtcrank ASC
      , discussion_comment.dcom_id DESC
    assuming that the comments table has a autoincrement id column as well

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think i tried that before...I'll try it again, but i don't think it worked for some reason.

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, tested that and it doesn't sort the second parameter there. dcom_id and jtcrank are both INT -- however at first, the jtcrank was TINYINT so i did an alter table and made it an INT.

    Should i delete that column and remake it?

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think the two fields that you ORDER BY have to match there type? Is that right? Welp, I deleted the jtcrank column and added it back. I made 3 topics 1,2,3. Does it matter if PRECISION is set to 11 for jtcrank field vs. PRECISION is set to "2" on the field I am comparing it too?

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT * 
    FROM discussion_topic AS dt
    INNER JOIN discussion_comment AS dc1
    ON dt.dtop_id = dc1.dcom_topic_id
    INNER JOIN
      (SELECT 
           dcom_topic_id
         , MAX(dcom_id) AS max_dcom_id
       FROM discussion_comment
       GROUP BY dcom_topic_id
      ) AS dc2
    ON  dc2.dcom_topic_id = d1.dcom_topic_id
    AND dc2.max_dcom_id = d1.dcom_id
    WHERE dt.rank > 0
    AND   dt.dtop_active = 1
    GROUP BY dt.dtop_id
    ORDER BY dt.jtcrank ASC
    Maybe you can even eliminate the final GROUP BY dt.dtop_id, because I think the query will give only one row for each dtop_id anyway.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by RobbieGoD View Post
    i think the two fields that you ORDER BY have to match there type?
    What do you mean? The fields you order by don't have to be all of the same type.

  8. #8
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I read that in order to do a ORDER a,b.

    a and b have to match.

    But that's all it said. It didnt say in what way it needed to match. So, who knows. I was not sure as that statement was kinda confusing.

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i will try out the SQL statement -- looks like a winner to me!

  10. #10
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey man, thanks alot! That worked great!

  11. #11
    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 RobbieGoD View Post
    I read that in order to do a ORDER a,b.

    a and b have to match.
    flat out wrong

    whatever you were reading, throw it away

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    166
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haha, well it was on the internet -- if i find the article again, I'll post it here.

    it wasn't really very clearly what they meant by "match".


Tags for this Thread

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
  •