CODEWITHSUNDEEP
mysql
KodeFor.Me
KodeFor.Me

Reputation: 13511

MySQL | How to get latest articles from each category?

I am using WordPress to build a score system for a company and I have to create a custom Query that not work in my case.

Here (http://sqlfiddle.com/#!2/1f510/1) I have a copy of my real data, as they are in my database, as well the query I am using to extract my data.

As you can see, I get four rows as a result. Each row corresponds to a category, but the issue is that I get the oldest post from each category instead of the latest that is required.

Any idea on how to modify this query in order to get the latest posts from each category ?

Upvotes: 1

Views: 440

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Here is one way...

http://sqlfiddle.com/#!2/1f510/34

padding padding padding

Upvotes: 1

जलजनक
जलजनक

Reputation: 3071

SELECT ID AS PostID,
       post_title AS PostTitle,
       meta_value AS CategoryID,
       name AS CategoryName,
       post_date AS `Date`
  FROM (SELECT *
          FROM wp_posts AS p
               INNER JOIN wp_postmeta AS m
                  ON p.ID = m.post_id
               INNER JOIN wp_terms AS t
                  ON m.meta_value = t.term_id
         WHERE m.meta_key = 'matchdayTeamsCategory'
        ORDER BY p.post_date DESC) tmpView
GROUP BY CategoryName;

Upvotes: 1

Related Questions