distinct with order by usage is not bringing unique values

[archy22]

I have written this query:

select distinct company_id,company_name from (select company_id, company_name from companies order by UPPER(company_name) asc);


It brings out an ordered company name list. But the values aren't unique.
Can you please help me understand what is wrong with the query?

I am using db2 V10.1 (LUW).

[Rabbit]

That should give you the distinct combinations of id and name. If you want just distinct name, then take id out of the query.

[archy22]

I want just distinct names, but need to display list of id,names in the results.
How do I do that?

Running this query will not fetch me distinct names as the id, name combination is taken together to check distinct probably.

[Rabbit]

If you need id, then you need to tell it which id to return because it is no longer distinct only on name if you include the id.

1, bob
2, bob
3, bob

There's only 1 name, but 3 different ids. If you want it distinct on name, it has no idea which id to return. So you have to tell it. Either min, max, or some other criteria that you choose.