Select last rows from MySQL WordPress

Question

I am trying to get last row with query to MySQL WordPress, The query is How much Post-Title with the title Germany each month, How can I reverse the result?

Query:

SELECT COUNT( p.id ) post_count, DATE_FORMAT( post_date,  '%Y-%m' ) post_month
FROM  `wp_posts` p
WHERE post_status =  'publish'
AND post_type = 'post'
AND post_title = 'Germany'
GROUP BY post_month
ORDER BY id DESC
LIMIT 3;

Now result:

46 2023-03
130 2023-02
51 2023-01

I needed the reverse result:

51 2023-01
130 2023-02
46 2023-03

I try the sub-query from here : Select last N rows from MySQL

But nothing works.

Thanks guys

Getting reverse result to MySQL query on WordPress

Answer 1

You need to order by date n ot by id

CREATE TABLE Table1
    (`count_` int, `date_` varchar(7))
;
    
INSERT INTO Table1
    (`count_`, `date_`)
VALUES
    (46, '2023-03'),
    (130, '2023-02'),
    (51, '2023-01')
;

Records: 3  Duplicates: 0  Warnings: 0

SELECT * FROM Table1 ORDEr By `date_`

count_	date_
51	2023-01
130	2023-02
46	2023-03

fiddle

So use

SELECT COUNT( p.id ) post_count, DATE_FORMAT( post_date,  '%Y-%m' ) post_month
FROM  `wp_posts` p
WHERE post_status =  'publish'
AND post_type = 'post'
AND post_title = 'Germany'
GROUP BY post_month
ORDER BY post_month
LIMIT 3;

or use if the previous query doesn't give you tee right answer

SELECT post_count, post_month
FROM
    (SELECT COUNT( p.id ) post_count, DATE_FORMAT( post_date,  '%Y-%m' ) post_month
    FROM  `wp_posts` p
    WHERE post_status =  'publish'
    AND post_type = 'post'
    AND post_title = 'Germany'
    GROUP BY post_month
    ORDER BY id
    LIMIT 3) t1
ORDER By post_month