CODEWITHSUNDEEP
phpmysqlsqlpdodistinct
user1537415
user1537415

Reputation:

SELECT DISTINCT still showing duplicates

Yes, there's a thousand questions about this on SO, but I've been searching for half an hour and I've yet to find a solution.

So, I've a table like this: enter image description here

And this is my query:

SELECT DISTINCT rengasID,leveys FROM renkaat ORDER BY leveys ASC

And this is the result I get:

enter image description here

If you get the idea, I'm populating a select field with it, but it still has duplicates.

What am I doing wrong?

Upvotes: 5

Views: 15022

Answers (3)

Xuntar
Xuntar

Reputation: 2280

Your rengasID is still different in each shown line. The distinct will check a mix of every selected field, so in this case it will search a distinct combination of rengasID and leveys.

You cannot ask for your ID here, since MySQL has no way of knowing which one you want.

Depending on what you want to do it can be more correct to save your "leveys" (I'm not sure what they are) in a separate table with a unique ID and join it. For filling up your list with all possible leveys, you can just query that new table. This can be important because using group by, you can get random results for id's later on.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you want distinct leveys, just choose that field:

SELECT DISTINCT leveys
FROM renkaat
ORDER BY leveys ASC

The rengasid has a different value on each row.

The distinct clause applies to all the columns being returned, regardless of parentheses.

EDIT:

If you need the regasid in the result, then use group by:

select leveys, min(regasid) as regasid
from renkaat
group by leveys
order by leveys asc;

This gives the first id. If you need all of them, you can get them in a list using group_concat(). If you need a separate id on each row, well, then you have duplicates.

Upvotes: 8

Bhushan
Bhushan

Reputation: 6181

This is because you are selecting combination of rengasID and leveys. And what you are getting as a result is a distinct combination of the two.

To achieve what you are trying, see the answer of @GordonLinoff.

Upvotes: 0

Related Questions