I am facing difficulties in a SQL query where I need to filter records based on a priority condition. If a record meets a priority condition (rn = 1), it should be returned, but if not, then checking for a secondary condition (rn2 = 1) should be done. What is the best way to structure this query?
Look at the code:
CTE_aux AS (
SELECT *,
CASE
WHEN status LIKE '%SIGNED%' THEN 1
ELSE 0
END AS rn
FROM Principal
),
CTE_aux2 AS (
SELECT *,
CASE
WHEN rn = 0 and status = 'ENDORSEMENT_SUCCESS' THEN 1
ELSE 0
END AS rn2
FROM CTE_aux
),
Consulta2 AS (
SELECT *
FROM CTE_aux2
WHERE rn = 1 OR (rn = 0 AND rn2 = 1)
)
If the status is ‘%SIGNED%’ RN = 1 it is OK, it must be returned. Now if it is RN = 0, a check is made to see if the status is ENDORSEMENT_SUCESS, if so, RN2 = returns 1.
So, I want the items that received RN =1 and the items that received RN = 0 and RN2 = 1 to be shown.
This is what results:
advertiser_id credito_id contract_id status rn rn2
dfb03613 5225 1409 ENDORSEMENT_SUCCESS 0 1
dfb03613 5225 1409 CONTRACTS_SIGNED 1 0
But this was not supposed to happen because if RN=1 it should not enter RN=2. STATUS SIGNED is checked first
14
As folks in the comments I also don’t see the problem you are facing. Please clarify if this SQL statement doesn’t return what you need.
WITH data AS (
SELECT
*,
CASE
WHEN status LIKE '%SIGNED%' THEN 1
WHEN status = 'ENDORSEMENT_SUCCESS' THEN 2
END AS rn,
ROW_NUMBER() OVER(ORDER BY CASE
WHEN status LIKE '%SIGNED%' THEN 1
WHEN status = 'ENDORSEMENT_SUCCESS' THEN 2 END) AS row_n
FROM Principal
WHERE (status LIKE '%SIGNED%' OR status = 'ENDORSEMENT_SUCCESS')
)
SELECT * FROM data
WHERE row_n = 1
You can easily split rn into two fields if needed.
5