SELECT DISTINCT problems

[hambil]

Originally Posted by Mistah Roth

My script displays the last 5 threads, a user has posted in. The main query is as follows:

SELECT distinct threadid FROM ". TABLE_PREFIX . "post WHERE userid = " . $vbulletin->userinfo['userid'] . " ORDER BY postid DESC LIMIT 5

That should work... because if you remove DISTINCT it shows the last posts you made including duplicates of the same thread if you posted more than once in a thread.

For some reason, when you add DISTINCT, it does not return the last 5 threads posted in. For some reason it skips any older threadids. If I remove the distinct from the query, it shows the last 5 posts properly. The problem is we can't have repeats, so DISTINCT is supposed to solve that.

Anyone have any idea whats going wrong? If all else fails I'll just make it so that it filters out doubles with PHP code.... not as efficient as I'd like it though, there should be no reason the query doesnt work.

DISTINCT does not work the way you think it does. What it is doing, if you look close enough, is eliminating all but the last duplicate row.

Now in your case, we can assume you have more than two posts to the thread with the id 140. Since the DISTINCT eliminates all but the last one, the oldest post is kept, not the newest, and 140 drops out of your top 15.

So, this gets a little tricky. I have to go do some stuff, but I'll work out the query for you and post it later tonight (early tomorrow).

There may be a way to do this with just SQL, but, I can't come up with it at the moment. You're going to need to process the results. Since you're dealing with all of a users threads this could be a large query result to step through.

Depending on your specific performance issues it might be more efficient to keep a separate table with userid and threadid that you update when a users posts. It would only keep the latest 5. You could also timestamp it and actually show the correct order if a user posts in a thread twice.

For example, I post in threadid 210, then I post in two other threads, then I post in 210 again. I wouldn't put 210 into the table twice, but I would update 210's timestamp.

Otherwise, you could just read in the entire result set (all their postid, threadids) and then loop through it until you have 15 unique threadids...