Reputation: 35
Using the following table. I am trying to get the Party_ID. I will have just the NEW_ORG_ID and ORIGINAL_ID.
Using the NEW_ORG_ID I want to get the PARTY_ID with the MAX(USR_CRTE_TS) unless the ORIGINAL_ID matches as well. Then I want the PARTY_ID where the NEW_ORG_ID and ORIGINAL_ID match.
PARTY_ID |DIRS_ID |SUPPLIER_ID|ORIGINAL_ID|TRIANGLE_ID|NEW_ORG_ID|USR_CRTE_TS |
------------------|---------|-----------|-----------|-----------|----------|-------------------|
5385606962 |476800490|5385606962 |NULL |NULL |69214683 |2018-09-27 04:56:13|
69214683 |476800490|NULL |2740793553 |NULL |69214683 |2018-09-27 04:54:15|
I tried the following. Keep in mind I hardcoded the NEW_ORG_ID and ORIGINAL_ID for this example only. In the real world example NEW_ORG_ID and ORIGINAL_ID are supplied by another part of the query.
SELECT DISTINCT PARTY_ID
FROM UNIQUE_IDS UNQ
WHERE
(
CASE
WHEN (
SELECT ts.PARTY_ID
FROM UNIQUE_IDS ts
WHERE (ts.NEW_ORG_ID = '69214683' AND ts.ORIGINAL_ID = '2740793553')
) IS NOT NULL
THEN
(
SELECT ts.PARTY_ID
FROM UNIQUE_IDS ts
WHERE (ts.NEW_ORG_ID = '69214683' AND ts.ORIGINAL_ID = '2740793553')
)
ELSE
(
SELECT ts.PARTY_ID
FROM UNIQUE_IDS ts
WHERE ts.NEW_ORG_ID = '69214683'
AND ts.USR_CRTE_TS = (
SELECT MAX(ts.USR_CRTE_TS)
FROM UNIQUE_IDS ts
WHERE ts.NEW_ORG_ID = '69214683'
)--xref.SBL_GLOB_CSTMR_ID)
)
END
) = UNQ.NEW_ORG_ID
;
Upvotes: 0
Views: 58
Reputation: 351403
You can use the ROW_NUMBER()
analytic function to get the "best" match (first by ORIGINAL_ID
, and then by USR_CRTE_TS
) marked with number 1, and filter on that:
SELECT PARTY_ID
FROM (
SELECT PARTY_ID,
USR_CRTE_TS,
ROW_NUMBER() OVER (
PARTITION BY PARTY_ID
ORDER BY CASE ORIGINAL_ID
WHEN '2740793553' THEN 0
ELSE 1
END,
USR_CRTE_TS DESC
) RN
FROM UNIQUE_IDS
WHERE NEW_ORG_ID = '69214683'
)
WHERE RN = 1
If USR_CRTE_TS
can be NULL
, then add another condition at the very end to be in line with what you had:
AND (ORIGINAL_ID = '2740793553' OR USR_CRTE_TS IS NOT NULL)
You can use the WITH
clause to build up your query, layer by layer.
So let's assume you already have working logic to select the two "parameters", then put that in a with
clause like this:
WITH CRIT(SEL_ORG_ID, SEL_ORIGINAL_ID) AS (
SELECT '69214683', '2740793553' FROM DUAL -- this is just an example
)
... and then continue the query like this:
), BASE AS (
SELECT UI.PARTY_ID,
UI.USR_CRTE_TS,
UI.ORIGINAL_ID,
CRIT.SEL_ORIGINAL_ID,
ROW_NUMBER() OVER (
PARTITION BY UI.PARTY_ID
ORDER BY CASE UI.ORIGINAL_ID
WHEN CRIT.SEL_ORIGINAL_ID THEN 0
ELSE 1
END,
UI.USR_CRTE_TS DESC
) RN
FROM UNIQUE_IDS UI,
CRIT
WHERE UI.NEW_ORG_ID = CRIT.SEL_NEW_ORG_ID
)
SELECT PARTY_ID
FROM BASE
WHERE RN = 1
AND (ORIGINAL_ID = SEL_ORIGINAL_ID OR USR_CRTE_TS IS NOT NULL)
Upvotes: 1
Reputation: 14858
I would use row_number()
with conditional ordering:
select party_id
from (
select party_id,
row_number() over (order by case when original_id = '2740793553' then 1 end,
usr_crte_ts desc) rn
from unique_ids unq
where new_org_id = '69214683')
where rn = 1
If original_id
matches then row has higher priority. Second priority is highest usr_crte_ts
. Finally we take first best row.
Upvotes: 0