SQL query to extract only the “current” wp_posts?

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.

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

get_posts(array(
             '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