SELECT DISTINCT still showing duplicates

Question

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:

And this is my query:

SELECT DISTINCT rengasID,leveys FROM renkaat ORDER BY leveys ASC

And this is the result I get:

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

What am I doing wrong?

Solution 1

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.

Solution 2

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.

Comments

[Admin]

Yes, that's what I want for this case. But for the future: What if I need to use rengasID also?

[Admin]

Oh yes. rengasID happens to be unique, so the query thinks that there is no duplicates.