StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>if your SQL supports the LIMIT clause, </p> <pre><code>SELECT m.author, m.date, t.topicname FROM messages m JOIN topics t ON m.topicid = t.topicid ORDER BY date desc LIMIT 1 </code></pre> <p>otherwise:</p> <pre><code>SELECT m.author, m.date, t.topicname FROM messages m JOIN topics t ON m.topicid = t.topicid WHERE m.date = (SELECT max(m2.date) from messages m2) </code></pre> <p>EDIT: if you want to combine this with the original query, it has to be rewritten using subqueries to extract the message count and the date of last message:</p> <pre><code>SELECT t.topicname, t.author, (select count(message) from messages m where m.topicid = t.topicid) AS messagecount, lm.author, lm.date FROM topics t INNER JOIN messages lm ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2) INNER JOIN boards b ON b.boardid = t.boardid WHERE b.boardid = 1 GROUP BY t.topicname </code></pre> <p>also notice that if you don't pick any field from table boards, you don't need the last join:</p> <pre><code>SELECT t.topicname, t.author, (select count(message) from messages m where m.topicid = t.topicid) AS messagecount, lm.author, lm.date FROM topics t INNER JOIN messages lm ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2) WHERE t.boardid = 1 GROUP BY t.topicname </code></pre> <p>EDIT: if mysql doesn't support subqueries in the field list, you can try this:</p> <pre><code>SELECT t.topicname, t.author, mc.messagecount, lm.author, lm.date FROM topics t JOIN (select m.topicid, count(*) as messagecount from messages m group by m.topicid) as mc ON mc.topicid = t.topicid JOIN messages lm ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2) WHERE t.boardid = 1 GROUP BY t.topicname </code></pre>

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