CODEWITHSUNDEEP
mysqluniquedistinct
Tobias Baumeister
Tobias Baumeister

Reputation: 2147

Distinct query not working

I've got a table like this (simplified):

table t
PostID --- Userid --- Length

PostID is unique, but the Userid isn't. A user may make several posts.
Now I wanted to create a ranklist of the longest Posts and did it like this:

SELECT DISTINCT(Userid), PostID FROM table t ORDER BY `Length` desc LIMIT 10

But this also gives me results like this:
PostID--- Userid --- Length
1 --- 100 --- 3,203
15 --- 942 --- 3,194
45 --- 100 --- 2,093

So a user made two posts that would be ranked. But I thought I avoid those duplicates with the DISTINCT-clause?
What am I doing wrong?

Upvotes: 0

Views: 74

Answers (2)

Drew MacInnis
Drew MacInnis

Reputation: 8567

DISTINCT is eliminating duplicate rows from the result. The two rows that have a Userid of 100 are not duplicates as the values in the other columns differ.

Upvotes: 0

Arthur Frankel
Arthur Frankel

Reputation: 4705

Sounds like logically you really don't want to do the DISTINCT since you are just looking to order by longest posts.

Don't you really just want to get the longest posts and sometimes the same user will be in the list multiple times? 

SELECT Userid, PostID FROM table t ORDER BY `Length` desc LIMIT 10

The reason your distinct isn't working is because you are also returning PostID. If you remove PostID then you will get a distinct UserID.

To do what I think you want you will need to do a subquery as mentioned here.

Upvotes: 1

Related Questions