SQL Query for getting all posts in their latest revised state

by

I’m exporting my WordPress posts data to an external tool* and I wanted to write a query that will get me the current published posts from the wp_posts table but as the latest revision (i.e. latest title, content, etc. but with the original post’s ID).

select * from wp_posts where post_parent = 0;

This gets me all the original posts, but only in the state when they were first created, without taking the revisions into account. Then I tried something like this.

select * from wp_posts where post_parent != 0 group by post_parent;

But this only gives me the first revision and not the latest. I guess it would also exclude posts that were never revised, which doesn’t work for me either.

I was thinking of joining the table on itself next, and seeing if I can filter it down to the max ID for a given post_parent, but wanted to know if anyone already had a solution before I try that.

How can I get all the posts, but with all their latest revisions applied? It must be possible since the site does when displaying posts.

*The tool in question is Solr, so I am looking to write a query to get all the fields I want to index, if you are curious.

1
1

Thanks to @AhmedMahdi’s comment I figured out the following works for my situation:

select * from wp_posts
where post_status="publish"
order by post_modified desc;

The where condition is necessary to remove all the draft or private posts that I am not concerned with.

Leave a Comment