Thread: How to remove duplicates in an array and maintain the order?
How to remove duplicated values in an array and maintain the order?
Regards,
David
On Sat, Jul 8, 2023 at 8:30 AM Shaozhong SHI <shishaozhong@gmail.com> wrote:
How to remove duplicated values in an array and maintain the order?
Use a for loop in pl/pgsql.
David J.
How to remove duplicated values in an array and maintain the order?
select array_agg(unnest) filter (where row_number = 1) from (select unnest, ordinality, row_number() over(partition by unnest order by ordinality) from (select * from unnest('{7,7,5,6,2,2,3,8,5,4,5}'::integer[]) with ordinality) x order by ordinality) x
{7,5,6,2,3,8,4}
regards
Marcos
Shaozhong SHI schrieb am 08.07.2023 um 17:30: > How to remove duplicated values in an array and maintain the order? You can use distinct on () select array_agg(val order by idx) from ( select distinct on (val) val, idx from unnest(array[7,7,5,6,2,2,3,8,7,5,4,1,5]) with ordinality as t(val,idx) order by val, idx ) x This picks the first occurrance of each element. If you want to get the last occurrance of each value use "order by val, idx desc" in the inner select