SELECT DISTINCT problems

[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.

[Zachariah]

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

I would say you need to add single quotes around " . $vbulletin->userinfo['userid'] . "

[Marco van Herwaarden]

Originally Posted by Zachariah

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

I would say you need to add single quotes around " . $vbulletin->userinfo['userid'] . "

It is a numeric column, so no quotes needed.

[sql]SELECT DISTINCT threadid FROM post WHERE userid = 1 ORDER BY dateline DESC LIMIT 5[/sql]

This seems to work correct. But might need to test more with different test data.

What MySQL version are you using?

[Mistah Roth]

MySQL version 4.1.18

If I run the following Query, here are my results (I increase the limit number to show)

SELECT distinct threadid FROM post WHERE userid = 1 ORDER BY postid DESC LIMIT 15

Gives me the following:

threadid
1355
140
1321
1338
140
1334
1333
1311
1332
1319
1311
1284
1312
1311
1308

Those are my actual last 15 posts. Now if I add distinct, this is what I get:

SELECT distinct threadid FROM post WHERE userid = 1 ORDER BY postid DESC LIMIT 15

threadid
1355
1321
1338
1334
1333
1332
1319
1284
1312
1311
1310
1309
1308
1300
1288

Notice all the lower threadid's are not included.

Any ideas?

[TECK]

For sure is not showing the same threadid's, you removed all duplicates so it gives room for other id's. For example 140 (2times), 1311 (3times), etc.
Also, you forgot to set a dateline in your query.
You must set a dateline, orelse you endup scanning the hole table for your id's, then drop all of them and keep only 5-15, whatever you want there.
This is very unorthodox, from a coder point of view, not to say very bad.

[Paul M]

I suggest you output the postids so you can see better what is going on.

[Mistah Roth]

Originally Posted by TECK

For sure is not showing the same threadid's, you removed all duplicates so it gives room for other id's. For example 140 (2times), 1311 (3times), etc.

It shows some doubles, 1311 is still in the second query, its just the lower values that get dropped, I don't know why?

Also, you forgot to set a dateline in your query.
You must set a dateline, orelse you endup scanning the hole table for your id's, then drop all of them and keep only 5-15, whatever you want there.
This is very unorthodox, from a coder point of view, not to say very bad.

Good call, thanks for pointing that out haha

[TECK]

It shows some doubles, 1311 is still in the second query, its just the lower values that get dropped, I don't know why?
Show me the query and the results.
Use EMS MySQL Manager to see all about your query (and post screenshots), it's free.

EMS SQL Manager 2005 Lite for MySQL, Windows edition (full installation package)
http://sqlmanager.net/en/products/my...nager/download

[Mistah Roth]

My 4th post in this thread has the queries and results.

[Cap'n Steve]

I don't know anything about DISTINCT, but this might work:

[sql]SELECT threadid FROM post WHERE userid = 1 GROUP BY threadid ORDER BY postid DESC LIMIT 5[/sql]