Remove duplicates with condition

[Anonymous]

Hi,

I have below scenario where I have to remove duplicates.  If my  ID column has duplicates and then look at the status column. If status column = Active or Inactive, then pick that row and remove other duplicate IDs for that row. I need to look my status column only when there r duplicates IDs. Example:

ID                           Status              Stage

000xnT2eYUTE     Active              To be billed

000xnT2eYUTE     Close               billed

000xnT2eYUTE     Close               billed

000xnT2eABCD    Inprogress        waived

So I want my output to be:

ID                           Status              Stage

000xnT2eYUTE     Active              To be billed

000xnT2eABCD    Inprogress        waived

How do I do it..?

[Anonymous]

Hi Monar,

Very intersting example

Do you know how to detect deplicate with a weight?

ie: if to detect that "Françoiss" is the same record as "francois"

Thank you

[xx_emanis]

Hi All,

I have a requirement where I need to load the Data from oracle table and load in in to a table in Snowflake database.

The source oracle table has duplicate records in it. I was asked to load distinct records in to the target table. Can you please let me know how to achieve this.

The Primary key in the source table has four columnsin it.

WIP_ENTITY_ID
OPERATIONS_SEQ_NUM
ORGANIZATION_ID
MAINT_COST_CATEGORY

Below query gives me duplicate records on teh source.

select WIP_ENTITY_ID
,OPERATIONS_SEQ_NUM
,ORGANIZATION_ID
,MAINT_COST_CATEGORY
,count(*)
from CST_EAM_WO_ESTIMATE_DETAILS
group by
WIP_ENTITY_ID
,OPERATIONS_SEQ_NUM
,ORGANIZATION_ID
,MAINT_COST_CATEGORY
having count(*) > 1;

Thanks in anticipation.