Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have this custom select query that pulls a specified number of posts from a specific category and displays them in the order of the latest comments. The problem is that if a post has both of the latest comments, it will get shown twice. I would like it to somehow detect duplicate posts and exclude them, showing only the most recent comment.

$sql =
"SELECT DISTINCT ID, post_title, post_password, comment_ID, comment_post_ID,
    comment_author, comment_date, comment_approved, comment_type,comment_author_url,
    SUBSTRING(comment_content,1,100) AS com_excerpt
FROM $wpdb->term_taxonomy as t1, $wpdb->posts, $wpdb->term_relationships as r1, $wpdb->comments
WHERE comment_approved = '1'
   AND comment_type = ''
   AND ID = comment_post_ID
   AND post_password = ''
   AND ID = r1.object_id
   AND r1.term_taxonomy_id = t1.term_taxonomy_id
   AND t1.taxonomy = 'category'
   AND t1.term_id IN ($inlist)
ORDER BY comment_date DESC LIMIT 4";

Any ideas?

share|improve this question
add comment

1 Answer 1

Remove

DISTINCT

And add

GROUP BY $wpdb->posts.ID

before 'ORDER BY'

share|improve this answer
    
Nope, didn't make any difference. –  Pippin Apr 29 '11 at 18:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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