unique value in column

[aebstract]

Okay, instead of bumping my topic from yesterday, I'm going to post a new one.. maybe it was confusing. Below is my query right now.

 

mysql_query("
SELECT *
FROM posts
JOIN forums ON ( forums.forum_id = posts.forum_id )
JOIN users ON ( users.user_id = posts.poster_id )
ORDER BY post_time DESC
LIMIT 4
");

 

I need to grab 4 rows from my database. These 4 rows have to have a unique value for posts.topic_id. I can probably figure out some loops and do it someway like that, but if I could get it in the query that would be great.

Thanks

[veridicus]

SELECT DISTINCT posts.post_id
FROM posts
JOIN forums ON ( forums.forum_id = posts.forum_id )
JOIN users ON ( users.user_id = posts.poster_id )
ORDER BY post_time DESC
LIMIT 4

 

And if you want the whole record (although this might be a performance hit):

SELECT * FROM posts WHERE post_id IN (
SELECT DISTINCT posts.post_id
FROM posts
JOIN forums ON ( forums.forum_id = posts.forum_id )
JOIN users ON ( users.user_id = posts.poster_id )
LIMIT 4)
ORDER BY post_time DESC

 

[aebstract]

On the second one:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

 

I am needing to pull all of the information, or if not all.. there is a quite a bit I will have to type out to get all I need. Either way, I think IN isn't supported.

 

 

Also, it's topic_id not post_id.. not a big deal though I can just change that

[veridicus]

Ok, how about

SELECT * FROM posts WHERE topic_id IN (
SELECT DISTINCT posts.topic_id
FROM posts
JOIN forums ON ( forums.forum_id = posts.forum_id )
JOIN users ON ( users.user_id = posts.poster_id )
ORDER BY post_time DESC 
)
LIMIT 4

 

Just remember that the subquery might be a performance hit because you're scanning and retrieving all of the records.  It would probably be better to just run the non-subquery version (the first answer), then loop through each topic_id in code and select each record, since you'll only need to query for 4 records.

[aebstract]

It grabbed 3 rows with the same topic_id, I might do some sort of loop, just end the loop once I accept 4 rows of unique post_id? Thanks for the help