Help to remove duplicates values from an array

[Former Member]

Hi

I am having to populate an  array of elements like a,b,c,d,e,f,...into a single column of tuple. Sometimes these elements can have duplicate values like a,b,c,d,a,b. Before moving the values into the table, I have to remove the duplicates. Please suggest how this can be done? The value that I would move after the change should be a,b,c,d without repeatin a and b again.

Could you please suggest how can we remove the duplicates?

Thank you

[Former Member]

Hi Sushma,

Considering that your data is present in a single column of a table. Please follow the below algorithm:-

1. Sort the data.

2. Use a ROW function to generate a row number value like 1,2,3 ......

3. Now your data will look like

VALUE ID

a 1

a 2

a 3

b 4

b 5

c 6

d 7

d 8

e 9

e 10

e 11

4. Write a query like:-

select * from table a where a.ID < any ( select b.ID from table b where a.value = b.value ) ;

Following the above steps will give you the result you are expecting. Infact you can write a single query and incorporate all the steps into it also. In that case the order will just reverse that means step 4 will be outermost then step 3 and so on.

Thanks

Sumonta