Custom MySQL query to list post

Question

You don’t need to use query_posts function. It could be done without it. Use setup_postdata function to set up global post data.

$sql = "
    SELECT
      p.*
    FROM
      orders_items oi
      INNER JOIN products pr
        ON pr.`post_id` = oi.`product_id`
      INNER JOIN posts p
        ON p.`ID` = pr.`post_id`
        AND p.`post_type` = 'product'
    GROUP BY oi.`product_id`
    ORDER BY COUNT(oi.id) DESC
";

echo '<ul>';

$result = $wpdb->get_results( $sql );
foreach ( $result as $post ):
    setup_postdata( $post );
    ?><li>
        <a href="https://wordpress.stackexchange.com/questions/79989/<?php the_permalink() ?>"><?php the_title() ?></a>
    </li><?php 
endforeach;

echo '</ul>';