Would like to select a row if a LOOKUP = COSTID. Else, get the row with the null field instead. Please see table below: (PAYCODE must be unique)

null LOOKUP will be selected for PAYCODE 202 because LOOKUP != COSTID as shown above.

The output must be:

My SQL so far:

SELECT 
    *
FROM 
    gl_table_db t1
WHERE 
    LOOKUP = COSTID OR LOOKUP IS NULL AND NOT EXISTS(SELECT * FROM gl_table_db t2 WHERE t1.LOOKUP = t2.LOOKUP AND t2.LOOKUP IS NOT NULL
    AND t1.LOOKUP IS NOT NULL)

I feel like this is very close but cannot quite get it. Thank you

CodePudding user response：

Here is a ROW_NUMBER based approach. We can assign a label of 1 for those records having the same values for LOOKUP and COSTID. A value of 0 can be assigned where either LOOKUP or COSTID be null, and -1 assigned to all other possibilities. Then, we can retain, for each PAYCODE, the record with the highest priority label value.

WITH cte AS (
    SELECT *, CASE WHEN LOOKUP = COSTID THEN 1
                   WHEN LOOKUP IS NULL OR COSTID IS NULL THEN 0
                   ELSE -1 END AS label
    FROM gl_table_db
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY PAYCODE ORDER BY label DESC) rn
    FROM cte
)

SELECT PAYCODE, LOOKUP, COSTID, ACCOUNT
FROM cte2
WHERE rn = 1;

CodePudding user response：

You were close (beware of parenthesis when mixing ANDs and ORs):

SELECT 
    *
FROM 
    gl_table_db t1
WHERE 
    -- Either we have the not-null version
    t1.LOOKUP = COSTID 
    OR 
    -- OR - if we don't- have the null version
    (t1.LOOKUP IS NULL 
     AND NOT EXISTS ( SELECT * 
                      FROM gl_table_db t2 
                      WHERE t1.PAYCODE = t2.PAYCODE
                        AND t2.LOOKUP=t1.COST_ID
                        )
    )