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:
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.
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.
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?
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.