CODEWITHSUNDEEP
phpmysqldatabase
user1267570
user1267570

Reputation: 211

Need to get most recent date of category change (post addtion, modification) for 10000 categories

I have 10000 categories of posts and 1200000 posts (each post has publication dae). I want to get date of most recent post for each category using one or two SQL queries. This is structure of database:

Categories
+--+----+
|id|name|
+--+----+

CategoriesToPosts
+--------+----+
|category|post|
+--------+----+

Posts
+--+------------+-   -+
|id|lastModified| ... |
+--+------------+-   -+

Upvotes: 2

Views: 62

Answers (1)

Mark Byers
Mark Byers

Reputation: 838696

Use a JOIN, GROUP BY and MAX:

SELECT
    CategoriesToPosts.category,
    MAX(Posts.lastModified) AS lastModified
FROM CategoriesToPosts
LEFT JOIN Posts
ON Posts.id = CategoriesToPosts.post
GROUP BY CategoriesToPosts.category

Upvotes: 2

Related Questions