Select only one single column.

Question

I think this is a simple question to solve, but I will investigate for an hour to answer. I have a table with three columns: "id", "id_x" and "name". But it is filled with duplicate values, for example:

id  id_x    name
1   100     Name_aaa
2   100     Name_aaa
3   100     Name_aaa
4   100     Name_aaa*
5   101     Name_bbb
6   101     Name_bbb*

Ok, ok, I did not create this table, and I can’t change them ... I just want to know what query I can execute to return only the values ​​"100 - Name_aaa" and "101 - name_bbb", ... note that after some names there is a "*". I would like to group only "id_x".

Is there a way to do this without using subqueries or joins?

Thanks in advance!

Answer 1

,

select id_x, min(name) as name
from table
group by id_x

Answer 2

,

id_x, tablename

Answer 3

select distinct
         id_x,
         case when name ~ E'\\*$' then substring(name, 1, length(name)-1)
              else name end
from t;

Answer 4

Or :

SELECT DISTINCT ON (id_x) id_x, name FROM table ORDER BY id_x, name;