Tom, I see your point. Which is valid considering there could be any value in col_y for some value in col_x. But in my case, col_y is a a function of col_x, i.e. two rows with the same value in row_x will have the same value in row_y as well.
Consider, you need to store some length values. Like this:
create table items (
id text,
len integer,
unit text
);
insert into items values (1,1,'mm');
insert into items values (2,5,'mm');
insert into items values (3,5,'mm');
insert into items values (4,1,'cm');
insert into items values (5,1,'cm');
insert into items values (6,1,'m');
insert into items values (7,1,'m');
insert into items values (7,2,'m');
insert into items values (8,2,'m');
insert into items values (9,5,'m');
With the view...
create view vu_items as
select
id,
len || unit as descr,
len*case unit when 'mm' then 1 when 'cm' then 10 when 'm' then 1000 end as len_mm
from items;
...I now want to have a list of all distinct descr ordered by length. But...
select
string_agg(descr,',' order by len_mm)
from vu_items;
...creates a list with duplicates, only:
1mm,5mm,5mm,1cm,1cm,1m,1m,2m,2m,5m
And...
select
string_agg(distinct descr,',' order by descr)
from vu_items;
...gives a list of distinct values, but in the wrong order:
1cm,1m,1mm,2m,5m,5mm
My solution to this (now) is:
select
regexp_replace(
string_agg(descr,',' order by len_mm),
'([^,]+)(,\1)?($|,)',
'\1\3',
'g'
)
from vu_items;
Thx again for your hint in the regexp_replacy issue in my other post...
Regards,
Ingolf