sql distinct join 3 tables

Question

I am having difficulty creating an SQL statement that selects the 5 most recent subcategories determined by when the content associated with the subcategory was recently created.

Subcategories Table:

subcategory_id	Title
33	Fitness
34	Evolution
35	Farming
36	Programming
37	Art
38	Funny

content_subcategories Table:

content_id	Subcategory_id
15	34
16	35
16	36
16	37
17	35
18	38
18	34
19	37

Content Table:

content_id	date
14	6-5-22
15	8-5-22
16	3-3-22
17	2-5-22
18	10-5-22
19	11-3-22

What I need (limit 10)

subcategory_id	subcategory_title	content_date (desc)
37	Art	11-3-22
38	Evolution	10-5-22
34	Funny	10-5-22
35	Farming	3-5-22
36	Programming	3-5-22

Notice Evolution does not appear twice so there is DISTINCT applied. The goal here is to pick 10 most recent content, somehow join to subcategory table via content_subcategory table. Remove any duplicate subcategory results.

Answer 1

Probably you're looking for something like

SELECT DISTINCT s.subcategory_id, s.Title, c.date
  FROM (
    SELECT subcategory_id, max(date) as last_entry
      FROM content c
     INNER JOIN content_subcategories cs
        ON c.content_id = cs.content_id
     GROUP BY subcategory_id
  ) latest 
 INNER JOIN content_subcategories cs ON latest.subcategory_id = cs.subcategory_id 
 INNER JOIN content c ON c.content_id = cs.content_id AND c.date = latest.last_entry
 INNER JOIN subcategories s ON s.subcategory_id = latest.subcategory_id
ORDER BY c.date DESC
LIMIT 5;

Edit: Corrected the limit to 5. Notice, that this is MySQL syntax (as you didn't provide any information about the DBMS you're using).