MySQL - Skip Duplicate WordPress Entries

Question

I'm writing a script to display the 10 most recently "active" WordPress blog posts (i.e. those with the most recent comments). Problem is, the list has a lot of duplicates. I'd like to weed out the duplicates. Is there an easy way to do this by changing the MySQL query (like IGNORE, WHERE) or some other means? Here's what I have so far:

<?php

function cd_recently_active() {
    global $wpdb, $comments, $comment;
    $number = 10; //how many recently active posts to display? enter here

if ( !$comments = wp_cache_get( 'recent_comments', 'widget' ) ) {
    $comments = $wpdb->get_results("SELECT comment_date, comment_author, comment_author_url, comment_ID, comment_post_ID, comment_content FROM $wpdb->comments WHERE comment_approved = '1' ORDER BY comment_date_gmt DESC LIMIT $number");

    wp_cache_add( 'recent_comments', $comments, 'widget' );
}
?>

Answer 1

Look at the DISTINCT option for the SELECT statement. Or alternatively the GROUP BY syntax (look at the same link). Though they work in different ways, these would be the two methods most likely to help you get exactly what you want.

Answer 2

I thought I had figured it out using GROUP BY, but now I'm not so sure. Here is the code:

    if ( !$comments = wp_cache_get( 'recent_comments', 'widget' ) ) {
 $comments = $wpdb->get_results("SELECT comment_post_ID, comment_author, comment_date FROM $wpdb->comments WHERE comment_approved = '1' GROUP BY comment_post_ID ORDER BY comment_date_gmt DESC LIMIT $number");
 wp_cache_add( 'recent_comments', $comments, 'widget' );
}

The single change is to add GROUP BY comment_post_ID (the field I wanted to be unique). Unfortunately, this "breaks" the function; it's frozen and does not update.

I also could not get DISTINCT to work. One comment I'm following up on to figure this out came from http://www.webmasterworld.com/forum88/9204.htm

In particular, comment #:1259236 by ergophobe says, "You left out the GROUP BY. Without that, you will get multiple results for a given topic_id b/c the row will not be distinct. In fact, the distinct is not necessary, just the GROUP BY."

Still looking....

Answer 3

this was fixed with an array. I'm not sure there is a way to do it with MySQL other than executing two separate queries.