Does not recognize ROW_NUMBER() function OVER (PARTITION BY

Question

Good afternoon

I am using the ROW_NUMBER() function OVER (PARTITION BY, to bring only unique values ​​of a concatenation of fields, however, when running the query, partition by works because it tells me the number of times the field that I indicate is repeated, but wanting to only bring a record from the list that appears it does not take it, so put only those with a value of 1 in my condition, it continues to bring me the entire data set.

 SELECT DISTINCT 
    C1_ID_ALERTA,
    C2_NOMBRE_USUARIO,
    C3_BP,
    C32_AREA_BANCARIA,
    C4_CONTRATO,
    C41_CENTRO_FINANCIERO,
    C5_CAMPO,
    C6_VALOR_ANTERIOR,
    C7_VALOR_ACTUAL,
    C8_FECHA_MODIFICACION,
    C9_HORA_MODIFICACION,
    C10_TIPO_LOG,
    enum
FROM (
    SELECT T1.*,ROW_NUMBER() OVER (PARTITION BY T1.C1_ID_ALERTA ORDER BY T1.C1_ID_ALERTA  DESC)as ENUM 
        FROM (
            SELECT 
            area_bancaria||contrato||valor_anterior||valor_actual C1_ID_ALERTA,
            nombre_usuario                         C2_NOMBRE_USUARIO,
            bp                                     C3_BP,
            area_bancaria                          C32_AREA_BANCARIA,
            contrato                               C4_CONTRATO,
            cenfinancierodes                       C41_CENTRO_FINANCIERO,
            campo                                  C5_CAMPO,
            valor_anterior                         C6_VALOR_ANTERIOR,
            valor_actual                           C7_VALOR_ACTUAL,
            fecha_modificacion                     C8_FECHA_MODIFICACION,
            hora_modificacion                      C9_HORA_MODIFICACION,
            tipo_log                               C10_TIPO_LOG
        FROM (SELECT  *
            FROM CAM_TBL_ALERTA_CONCENTRADO conc
               INNER JOIN  IBM_I2.I2_CONTRATOS cont
                 ON conc.contrato = LTRIM(cont.nocont,'0')  
                 AND cont.institucion = conc.area_bancaria        
                   WHERE   
                        TIPO_LOG='LOG BP' 
                        AND OPERACION='2'  
                )              
            )T1
        ) T2      
                 WHERE  ENUM=1
                 AND C1_ID_ALERTA IS NOT NULL 
                    AND C10_TIPO_LOG='LOG BP'  
                    AND C5_CAMPO NOT IN ('GIRO_CNBV','BIRTHDT','D_F_ESCRITURA','CLAVE_COL',
                                        'CONSNUMBER','XSEXF','ADDRNUMBER','NAME_ORG1','NAME_ORG2','NAME_ORG3','TAXNUM',
                                        'RELTYP','ADDR_TYPE','NAME_LAST','NAME_LST2','NAMEMIDDLE','NAME_FIRST',
                                        'STREET','POST_CODE1','HOUSE_NUM1','LOCALIDAD','REGION','ROOMNUMBER',
                                        'TOWN','CLAVE_COL','ADDR_TYPE','RELTYP')
                    OR c5_campo='SMTP_ADDR'
                    OR c5_campo='TEL_NUMBER'

It doesn't mark any error.

Answer 1

Condition precedence rules say AND is evaluated before OR. So effectively you have:

WHERE
(
  ENUM=1
  AND C1_ID_ALERTA IS NOT NULL 
  AND C10_TIPO_LOG='LOG BP'  
  AND C5_CAMPO NOT IN (...)
)
OR c5_campo='SMTP_ADDR'
OR c5_campo='TEL_NUMBER'

If either of those last two conditions is true, then that result will be included regardless of the ENUM value, because of the ORs.

You probably want some other order of evaluation, so you should add parentheses to get the order you need; possibly:

WHERE ENUM=1
AND C1_ID_ALERTA IS NOT NULL 
AND
(
  (
    C10_TIPO_LOG='LOG BP'  
    AND C5_CAMPO NOT IN (...)
  )
  OR c5_campo='SMTP_ADDR'
  OR c5_campo='TEL_NUMBER'
)

but that's just a guess...