title: Get most recent result from a LEFT JOIN column
body: <p>I'm creating a custom forum from scratch and I'm attempting to use some <code>LEFT JOIN</code> queries to get information such as <code>total posts</code>, <code>total threads</code> and most <code>recent thread</code>. I've managed to get the data but the <code>recent thread</code> keeps returning a random value rather than the most recent thread.</p>
  <pre><code>CREATE TABLE forum_categories
      (`name` varchar(18), `label` varchar(52), `id` int)
  ;
      
  INSERT INTO forum_categories
      (`name`, `label`, `id`)
  VALUES
      ('General Discussion', 'Talk about anything and everything Digimon!', 1),
      ('Deck Discussion', 'Talk about Digimon TCG Decks and Strategies!', 2),
      ('Card Discussion', 'Talk about Digimon TCG Cards!', 3),
      ('Website Feedback', 'A place to discuss and offer feedback on the website', 4)
  ;
  
  CREATE TABLE forum_topics
      (`name` varchar(18), `id` int, `parent_id` int, `author_id` int, date date)
  ;
      
  INSERT INTO forum_topics
      (`name`, `id`, `parent_id`, `author_id`, `date`)
  VALUES
      ('My First Topic', 1, 1, 16, '2021-03-29'),
      ('My Second Topic', 2, 1, 16, '2021-03-30')
  ;
  
  CREATE TABLE forum_topics_content
      (`id` int, `topic_id` int, `author_id` int, date datetime, `content` varchar(300))
  ;
      
  INSERT INTO forum_topics_content
      (`id`, `topic_id`, `author_id`, `date`, `content`)
  VALUES
      (1, 1, 16, '2021-03-29 15:46:55', 'Hey guys! This is my first post!'),
      (2, 1, 16, '2021-03-30 08:05:13', 'This is my first topic reply!')
  ;
  </code></pre>
  <p>My Query:</p>
  <pre><code>SELECT forum_categories.name, label, forum_categories.id, COUNT(DISTINCT(forum_topics.id)) as 'topics', COUNT(DISTINCT(forum_topics_content.id)) as 'posts', SUBSTRING(forum_topics.name,1, 32) as 'thread'
  FROM forum_categories 
  LEFT JOIN forum_topics ON forum_categories.id = forum_topics.parent_id
  LEFT JOIN forum_topics_content ON forum_topics.id = forum_topics_content.topic_id
  GROUP BY forum_categories.id
  ORDER BY forum_categories.id, forum_topics.date DESC
  </code></pre>
  <p>I figured having an <code>ORDER BY</code> of <code>forum_topics.date DESC</code> would work for me and output the most recent thread which is <code>&quot;My Second Topic&quot;</code> but it doesn't.
  I'm a bit stumped and have tried different variations of <code>ORDER BY</code> to no avail.
  <code>thread</code> keeps returning a random result from the two possible results.</p>
  <p>Full example with data is available on this fiddle: <a href="https://www.db-fiddle.com/f/auDzUABaEpYzLKDkRqE7ok/0" rel="nofollow noreferrer">https://www.db-fiddle.com/f/auDzUABaEpYzLKDkRqE7ok/0</a></p>
  <p><strong>Desired result</strong> would <code>'thread'</code> always being the latest thread which in this example is <code>&quot;My Second Topic&quot;</code>. However it always seems to randomly pick between <code>&quot;My First Topic&quot;</code> and <code>&quot;My Second Topic&quot;</code>.</p>
  <p>The output for the first row should always be:</p>
  <pre><code>'General Discussion' , 'Talk about anything and everything Digimon!' 1, 2, 2, 'My Second Topic'
  </code></pre>