Обсуждение: How to remove duplicates in an array and maintain the order?

Поиск
Список
Период
Сортировка

How to remove duplicates in an array and maintain the order?

От
Shaozhong SHI
Дата:
How to remove duplicated values in an array and maintain the order?

Regards,

David

Re: How to remove duplicates in an array and maintain the order?

От
"David G. Johnston"
Дата:
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.

Re: How to remove duplicates in an array and maintain the order?

От
Marcos Pegoraro
Дата:
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 

Re: How to remove duplicates in an array and maintain the order?

От
Thomas Kellerer
Дата:

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