CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
KodeFor.Me
KodeFor.Me

Reputation: 13511

MySQL | Select distincted records

I have that base : http://sqlfiddle.com/#!2/e5a24/2 that is a shorthand of WordPress default schema. I have cut out the fields that are not required for that example.

As you can see, in the results I have two times the "Category 1". What I like to do, is to get only the latest instance of the "Category 1".

The correct result for that SQL is the following:

POSTID  POSTTITLE   CATEGORYID  CATEGORYNAME    DATE
--------------------------------------------------------------
2   2st Game    2   Category 2  January, 01 2013 00:00:00+0000
3   1st Game    1   Category 1  January, 15 2013 00:00:00+0000

I have try the Group By "CategoryID" but while I get only one instance of the Category 1 in the results, I get the older one, not the most early record that belong to "Category 1".

Any idea about that, and how can I fix it ?

Upvotes: 0

Views: 69

Answers (3)

SP007
SP007

Reputation: 1921

use order by date DESC to get "Category 1" latest

Upvotes: 2

John Woo
John Woo

Reputation: 263693

SELECT  a.ID AS PostID,
        a.post_title AS PostTitle,
        c.meta_value AS CategoryID,
        d.name AS CategoryName,
        a.post_date AS Date
FROM    wp_posts a
        INNER JOIN
        (
            SELECT  post_title, MAX(post_date) max_date
            FROM    wp_posts
            GROUP   BY post_title
        ) b ON a.post_title = b.post_title AND
                a.post_date = b.max_date
        INNER JOIN  wp_postmeta c
            ON a.ID = c.post_ID
        INNER JOIN wp_terms d
            ON c.meta_value = d.term_ID
WHERE   c.meta_key = 'matchdayTeamsCategory'

Upvotes: 4

Hassan Voyeau
Hassan Voyeau

Reputation: 3624

You have to use MIN(Date) with your GROUP BY

Upvotes: 2

Related Questions