I recently wrote a custom WordPress query to retrieve the most popular post (based on # of comments) on my site and give me access to the post title, excerpt, slug, date, and # of comments. The following code can go inside or outside The Loop. It will work both ways. You will also need to add the appropriate formatting for your site to make it look good, of course.
<?php
$hotTopic = $wpdb->get_row("SELECT count(comment_id) as comments, comment_post_id, wp_posts.post_title, wp_posts.post_excerpt, wp_posts.post_name, wp_posts.post_date FROM wp_comments LEFT JOIN wp_posts on comment_post_id=wp_posts.ID WHERE comment_approved != 'spam' GROUP BY comment_post_id ORDER by comments DESC LIMIT 1");
if (isset($hotTopic) || $hotTopic) {
echo $hotTopic->post_title; // Post Title
echo $hotTopic->comments; // # Comments
echo trim($hotTopic->post_excerpt); // Post Excerpt
echo$hotTopic->post_name; // Post Slug
echo date('l, F jS, Y',strtotime($hotTopic->post_date)); // Post Date w/ formatting
}
?>
– Chris
http://www.christopherjason.com/
