Question 20432 in Mysql Question posted in
The official MySQL documentation can be found here.

Select last rows from MySQL WordPress

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    From your expected result, it seems to me that you’re trying to order by the post_month column in an ascending order.

    So I think the query you’re looking for is rather:

    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 ASC
LIMIT 3;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search