only - sql join first record

Oracle Join Condition con Top 1

Question

Intenté encontrar una solución a esto, incluido el uso del método row_number () dentro de mi unión, pero de alguna manera no puedo obtener el resultado deseado.

El problema que estoy teniendo ahora se basa en el hecho de que tengo algunos registros de la tabla de unión que tienen varias instancias (1 a muchos) con el mismo criterio que está causando duplicados, por lo que aunque pensé que podría usar un mínimo () o max () en una fecha, los casos en los que tengo el mismo EffectDT salen dos veces.

Además de esto , si tengo instancias en las que no tengo un registro "activo" (XPIRDT NO ES NULO), necesito extraer el registro caducado, así que usaría la declaración "O" o simplemente realizaría otra combinación para obtener el registro caducado en el caso de que la primera unión no produzca ningún registro y tenga una condición de caso para evaluar el valor antes de la visualización?

Así que aquí hay una instancia de datos de muestra con los que estoy tratando:

Por lo tanto, lo que sigue con la muestra de datos anterior todavía produce 2 registros, que puedo eliminar uno evaluando la fecha de caducidad, PERO presentaré problemas si todos los registros están caducados, por lo que no recuperaré nada.

 LEFT OUTER JOIN PARTYXREF
ON MBR_PERSON.NAMEID = PARTYXREF.NAMEID
AND PARTYXREF.REFTYPE LIKE ''COMM''
LEFT JOIN (
SELECT *
FROM ADDRDATA TMP
WHERE TMP.ADDRTYPE = ''2''
AND TMP.EFFECTDT = (SELECT MAX(EFFECTDT) FROM ADDRDATA TMP2 WHERE TMP2.ADDRID = TMP.ADDRID)
) MBR_ADDR
ON PARTYXREF.REFKEY = MBR_ADDR.ADDRID 

También intenté usar lo siguiente dentro de mi declaración "JOIN", sin embargo, de alguna manera no puedo hacer uso de la clave de unión (REFKEY) dentro de donde tengo el valor codificado aquí, así que esto realmente funciona, pero no puede parecer para incorporar en la declaración de combinación.

SELECT ADDR.*
FROM (SELECT tmp.*, row_number() OVER (ORDER BY XPIRDT DESC) AS SEQNUM
FROM ADDRDATA tmp
WHERE tmp.ADDRTYPE = ''2''
AND tmp.ADDRID = 10948448
) ADDR
WHERE SEQNUM = 1

¡He perdido una hora en esto, así que necesito otra perspectiva por favor! :)

Answer 1

Todavía no estoy seguro de qué registro, en su ejemplo, quería, así que déjeme darle algunas alternativas.

De su declaración del problema, deduje que desea la fecha de vigencia más reciente de las disponibles. Como hay vínculos involucrados, no puede usar max() (como ya ha indicado), y row_number() es el camino a seguir:

with cte as (
select
addrid, effectdt, xpirdt,
row_number() over (partition by addrid order by effectdt desc) as rn
from addrdata
)
select
addrid, effectdt, xpirdt
from cte
where rn = 1

La siguiente parte fue donde me perdiste con los nulos ... Si tu clasificación secundaria es por fecha de vencimiento y quieres que un valor nulo supere la última fecha de vencimiento, entonces ordenarás antes de la fecha de vencimiento y pondrás nulls first :

with cte as (
select
addrid, effectdt, xpirdt,
row_number() over
(partition by addrid order by effectdt desc, xpirdt nulls first) as rn
from addrdata
)
select
addrid, effectdt, xpirdt
from cte
where rn = 1

Lo que significa que esta fila es la ganadora:

10948448 5/14/2015 <null>

Sin embargo, si desea que los valores nulos se consideren solo si no hay fechas de caducidad, puede utilizar nulls last (u omitirlo, ya que es el valor predeterminado):

with cte as (
select
addrid, effectdt, xpirdt,
row_number() over
(partition by addrid order by effectdt desc, xpirdt nulls last) as rn
from addrdata
)
select
addrid, effectdt, xpirdt
from cte
where rn = 1

Lo que significa que este tipo ha ganado el premio:

10948448 5/14/2015 5/13/2015

Como usa row_number() , no perderá ninguna fila; se garantiza que cada fila tenga un número de fila. Es solo que si hay lazos verdaderos, entonces es una cuestión de elegir la fila. Sin embargo, su problema con fechas de caducidad nulas no debería causar ningún problema con este enfoque.

- editar 13/02/16 -

Creo que estoy empezando a comprender tu problema, pero no estoy 100% seguro. He incorporado fragmentos de tu código con la combinación de la izquierda con mi sugerencia, y la necesidad de tener fechas de caducidad nulas primero, y esta es mi próxima grieta:

with cte as (
select
addrid, effectdt, xpirdt,
row_number() over
(partition by addrid order by effectdt desc, xpirdt nulls first) as rn
from addrdata
)
select
cte.addrid, effectdt, xpirdt
from
mbr_person mb
left join partyxref px on
mb.nameid = px.nameid and
px.reftype = ''COMM''
left join cte on
px.refkey = cte.addrid and
cte.rn = 1

Asumiendo que esto no lo hace:

1. Cuando dice que un XPIRDT nulo tiene prioridad, ¿se refiere incluso a fechas de vigencia más recientes, o es solo un desempate para el EFFECTDT más reciente? Si esto último, entonces lo que tengo debería funcionar. Si el primero, entonces tenemos que cambiar el orden por en la función analítica
2. Estoy totalmente adivinando cuando se trata de las tablas partyxref y mbr_person . Si esto no funciona, tal vez publique algunos datos de muestra y resultados deseados para incluir esas dos tablas o virarlas.