[SOLVED] Mysql distinct join

[tradet]

Mysql noob calling for assistance here.

 

SELECT DISTINCT comments.blog_id, blog.title
FROM comments, blog
WHERE comments.blog_id = blog.id
ORDER BY comments.date_entered DESC
LIMIT 6 

 

Here's my tables

 

comments:

id

blog_id

ip

date_entered

user_id

entry

name

link

 

blog:

id

title

entry

date_entered

 

comments.blog_id points to it's corresponding blog.id ofc.

What I want on my site is one "Latest comments" field with the newest comments at the top and links to the blog post where someone left the comment.

 

But with this query I don't get it sorted by date_entered from the comments. If I instead do:

 SELECT DISTINCT comments.blog_id, comments.date_entered, blog.title
FROM comments, blog
WHERE comments.blog_id = blog.id
ORDER BY comments.date_entered DESC
LIMIT 6 

I get it sorted but now the blog_id isn't unique.

 

Edit: mysql version 5.xx something

[fenway]

You can't use DISTINCT like that... you have to find the most recent comment first, then join back.

[tradet]

Ehm...

 

Don't know how to

[tradet]

Got it working with

SELECT DISTINCT comments.blog_id, blog.title FROM comments JOIN blog WHERE comments.blog_id = blog.id ORDER BY comments.date_entered DESC LIMIT 6