Getting latest posts in a category on a forum

Question

Am trying to make an SQL query that will fetch every category from a DB and join them with the latest post in the category.

I use MySQL

The hierarchy of the contents is as so

Category > Forum > Posts

and this is a description of these tables

Categories

+-------+---------------------------+------+-----+---------+----------------+
| Field | Type                      | Null | Key | Default | Extra          |
+-------+---------------------------+------+-----+---------+----------------+
| id    | int(10) unsigned          | NO   | PRI | NULL    | auto_increment |
| title | varchar(100)              | NO   |     | NULL    |                |
| icon  | varchar(100)              | NO   |     | NULL    |                |
| color | set('red','green','pink') | NO   |     | NULL    |                |
+-------+---------------------------+------+-----+---------+----------------+

Forums

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100)     | NO   |     | NULL    |                |
| subtitle    | varchar(100)     | NO   |     | NULL    |                |
| category_id | int(10) unsigned | NO   | MUL | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Posts

+------------+------------------+------+-----+------------------------+
| Field      | Type             | Null | Key | Default|  Extra        |
+------------+------------------+------+-----+--------+---------------+
| id         | int(10) unsigned | NO   | PRI | NULL   |auto_increment |
| title      | varchar(100)     | NO   |     | NULL   |               |
| content    | longtext         | NO   |     | NULL   |               |
| forum_id   | int(10) unsigned | NO   | MUL | NULL   |               |
| slug       | varchar(100)     | NO   | MUL | NULL   |               |
| created_at | timestamp        | NO   |     | TS     |               |
| updated_at | timestamp        | NO   |     | 0000   | on update CTP |
+------------+------------------+------+-----+------------------------+

So to get the latest posts from each category, I have been able to make a query that actually gets what I want, but I don't think its the best possible way, will appreciate a smarter way, this is what I came up with

SELECT *
FROM   (SELECT `categories`.`id`,
               `posts`.`title`      AS post_title,
               `categories`.`title` AS cat_title,
               `posts`.`created_at` AS created
        FROM   `categories`
               JOIN `forums`
                 ON `forums`.`category_id` = `categories`.`id`
               JOIN `posts`
                 ON `posts`.`forum_id` = `forums`.`id`
        ORDER  BY `created` DESC
        LIMIT  18446744073709551615) AS sub
GROUP  BY `id`  

===================EDIT====================

Sample Category Table

+------------------------+-----------+----------+
| id| title              | icon      | color    |
+------------------------+-----------+----------+
| 1 | General Forums     | fa-pencil | red      |
| 2 | Help & Disscussion | fa-person | blue     | 
+---+--------------------+--------+------+------+

Sample Forum Table

+------------------------------+----------------------+--------------+
| id| title                    | subtitle             | category_id  |
+------------------------------+----------------------+--------------+
| 1 | Software Development     | About software dev   | 1            |
+---|--------------------------|----------------------|--------------|
| 2 | Graphics Design          | About graphics des   | 2            |
+---+--------------------------+-----------+----------+--------------|

Sample Post Table

+---+---------------------+----------------------------+----------------------------------+
| id| title               | content         | forum_id | slug       | created_at          |
+-------------------------+----------------------------+----------------------------------+
| 1 | Memoizing in JS     | Lorem Ipsum     | 1        |  memo-js   | 2019-03-21 00:45:54 |
+---+---------------------+----------------------------+------------+---------------------+
| 2 | Using headers in C# | Lorem Ipsum     | 1        | using-he   | 2019-03-20 00:45:54 |
+---+---------------------+-----------------+----------+----------------------------------+

So for each category, i need the latest post based on date created

This is a dump of the database here

Answer 1

This is absolutely not the correct way. You are using GROUP BY with SELECT * which is not supported by most databases and not even supported by more recent versions of MySQL using the default settings.

Instead:

SELECT c.id, p.title as post_title, c.title as cat_title,
       p.created_at AS created
FROM categories c JOIN
     forums f
     ON f.category_id = c.id JOIN
     posts p
     ON p.forum_id = f.id
WHERE p.created_at = (SELECT MAX(p2.created_at)
                      FROM posts p2 JOIN
                           forums f2
                           ON p2.forum_id = f2.id
                      WHERE f2.category_id = f.category_id
                     )
ORDER  BY created_at DESC;

The subquery is calculating the maximum created at date for the posts for a given category.

Answer 2

This should give what you want.

SELECT *
FROM (
  SELECT c.id, p.title AS post_title, c.title AS cat_title, p.created_at AS created
      , ROW_NUMBER() OVER(PARTITION BY c.id ORDER BY p.created_at) AS rn
  FROM categories c
  JOIN forums f ON f.category_id = c.id
  JOIN posts p ON p.forum_id = f.id
) a
WHERE rn = 1