StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I could be wrong but I don't think your query returns <strong>all the comments</strong> for the topic. Since you're grouping by the topic_id, it's going to collapse it by topic and return just one row and comment per topic. If you want all the comments for each topic sorted by topic and timestamp, you can use this query.</p> <pre><code>SELECT topic.topic_title,topic.topic_id,comments.* FROM comments JOIN topic ON topic.topic_id=comments.topic_id WHERE comments.user='$user' AND comments.timestamp>$week ORDER by topic.topic_title,comments.timestamp desc </code></pre> <p>If what you're trying to do is group by the topic and return the latest comment for each topic, then you can use the following query. The query gets the latest comment for each topic and grabs for each topic the entire comments row matching that latest timestamp.</p> <pre><code>SELECT latest_ts.topic_title,latest_ts.topic_id,comments.* FROM comments JOIN (SELECT topic.topic_title,topic.topic_id,max(comments.timestamp) latest_timestamp FROM comments JOIN topic ON topic.topic_id=comments.topic_id WHERE comments.user=$user AND comments.timestamp>$week GROUP BY topic.topic_title,topic.topic_title) latest_ts ON latest_ts.latest_timestamp = comments.timestamp AND latest_ts.topic_id = comments.topic_id ORDER by latest_ts.topic_title </code></pre> <p>The query above could return two rows per topic in the unlikely event that the last two comments for a given topic have the same timestamp. If this is a concern, you will need to do a little extra work wrapping this output in another round of grouping to lose the unneeded rows.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload