The wp_posts table seems to retain all revisions of the same, ok very similar but presumably different, posts/pages/whatever.

I'm somewhat conversant with SQL but not WordPress. I need to extract just those records which would appear on the public facing site; so just the most recent revision, and not all the superceded rows. Not sure how to filter the fields. Obviously something more complicated than:

select *
from wp_posts
where post_status in ('publish','revision')
order by post_modified desc

which has 'duplicates' and seems to miss some stuff.

  • 1
    Do you need to do this via SQL? Why not WordPress functions?
    – Rarst
    Sep 14, 2011 at 10:11
  • +1 on @Rarst. Plus: If you get duplicates, use DISTINCT.
    – kaiser
    Sep 14, 2011 at 11:02

2 Answers 2


Rather than constructing query from scratch, it is easier to see what exactly is WordPress querying when API function is used:

             'numberposts' => -1,

var_dump( $wpdb->last_query );

Gives following SQL:

SELECT wp_posts.* FROM wp_posts 
WHERE 1=1 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish')
ORDER BY wp_posts.post_date DESC
  • @Elliott yeah, I wish someone had told me about that trick couple years earlier than I learned it :)
    – Rarst
    Sep 14, 2011 at 12:15
  • You should always use wpdb to get the current table prefix. Sep 14, 2011 at 13:16
  • @Brian Fegter this query is generated by WP and so already has correct prefix in place. This approach will always give query, specific to installation.
    – Rarst
    Sep 14, 2011 at 13:19
  • @Rarst - DOH :) Sep 14, 2011 at 13:20
  • Awesome! So it's really just looking for a status of "publish". It must set to something else if the post is updated. Thx.
    – John Mee
    Sep 15, 2011 at 9:32

You should use this as Wordpress does heavy lifting here:

$args = array(
    'showposts' => 10,
$posts = query_posts();

You learn more about adding arguments to query_posts http://codex.wordpress.org/Class_Reference/WP_Query

You should also use the Wordpress database class when creating manual MYSQL queries. You can learn more here: http://codex.wordpress.org/Class_Reference/wpdb Wordpress also includes a great function for escaping data in queries to keep your operation safe: esc_sql($string);

You can use the Wordpress db class like this:

global $wpdb;
$results = $wpdb->get_results("SELECT * FROM $wpdb->posts WHERE $wpdb->posts.post_status = 'publish'");

Using $wpdb->before your the table name prepends the assigned database prefix. This is important because you can have multiple Wordpress installations in one database using table prefixes. $wpdb-> will grab the correct prefix context.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.