MySQL Group By for sorted table

I’ve two tables with these structures:

Table 1 (comments)

+----+---------+------------+--------+---------+
| id | post_id | created_at | author | content |
+----+---------+------------+--------+---------+
|  1 |       3 | 03-01-2020 | Jack   | XXX     |
|  2 |       1 | 02-29-2020 | Joe    | XXX     |
|  3 |       3 | 02-24-2020 | Anna   | XXX     |
|  4 |       2 | 03-01-2020 | Mia    | XXX     |
+----+---------+------------+--------+---------+

Table 2 (posts)

+-----+-------------+---------------------+---------+
| id  | category_id |        name         | content |
+-----+-------------+---------------------+---------+
|   1 |           1 | some random name    | xxxxxxx |
|   2 |           2 | another random name | xxxxxxx |
|   3 |           1 | third random name   | xxxxxxx |
+-----+-------------+---------------------+---------+

My goal is to get a list with the latest comment from each post of a specific category_id.

So for example, if I have category 1 as input I need the comments with the id 2 and 1 because these are always the last comments for each post with the given category.

Until now, I got this query which returns all entries ordered by the creation date.

select *
from comments c
join posts p on c.post_id = p.id
where p.category_id = 1
order by c.created_at desc;

I’ve read about the group by property but this does not guarantee me to get the last entries. How can I solve this problem?

Answer

Assuming that Table2 is the categories table, if you want in the results only the columns of Table1 then you don’t need a join.
Use NOT EXISTS:

select t1.* 
from table_1 t1
where not exists (
  select 1 from table_1
  where table2_id = t1.table2_id and created_at > t1.created_at
)

or with a correlated subquery:

select t1.* 
from table_1 t1
where t1.created_at = (select max(created_at) from table_1 where table2_id = t1.table2_id)

Edit:

select c.*
from posts p inner join (
  select c.* from comments c
  where not exists (
    select 1 from comments
    where post_id = c.post_id and created_at > c.created_at
  )
) c on c.post_id = p.id
where p.category_id = ?

See the demo.
Or:

select c.*
from posts p inner join (
  select c.* from comments c
  where c.post_id in (select id from posts where category_id = ?)
  and not exists (
    select 1 from comments
    where post_id = c.post_id and created_at > c.created_at
  )
) c on c.post_id = p.id

See the demo.
Replace ? with the category_id you want to search for.
Results:

| id  | post_id | created_at          | author | content |
| --- | ------- | ------------------- | ------ | ------- |
| 1   | 3       | 2020-03-01 00:00:00 | Jack   | XXX     |
| 2   | 1       | 2020-02-29 00:00:00 | Joe    | XXX     |