DB2 SQL Return One Row from Left Joined Table Based on Custom Weighting

I have two tables, ‘INVOICES’ contains invoice information and ‘CSEML’ contains customer email addresses. The email address type (field EMLTP) could be designated as ‘AP’ (Accounts Payable), ‘PO’ (Purchasing), or blank (default). Each customer could have 3 email addresses in CSEML (one for each designation). I’d like to be able to do this within the query: join the invoice table to the email table and pull a maximum of one email address per invoice – the AP address if it is defined, otherwise the PO address if it is defined, otherwise the default address if defined, otherwise null.

CSEML is structured like this

CUSTNO,EMLTP,EMAIL
000989367,PO,po@example.com
000254785,PO,po@example3.com
000989367,,default@example.com
000112354,,default@example2.com
000989367,AP,ap@example.com
000254785,,default@example3.com

Right now I have the following query

SELECT I.INVNO, I.CUSTNO, E.EMAIL 
FROM INVOICES I 
LEFT JOIN CSEML E ON I.CUSTNO = E.CUSTNO
WHERE I.INVNO = '10124'

Which returns something like this when there are all 3 addresses defined:

10124    000989367    po@example.com
10124    000989367    default@example.com
10124    000989367    ap@example.com

How can I assign a weight to the different email ‘types’ so that I’m only getting the most preferred address available?

Working Code based on accepted answer

SELECT INVNO, CUSTNO, EMAIL
FROM(
    SELECT I.INVNO, I.CUSTNO, E.EMAIL, ROW_NUMBER() OVER (PARTITION BY I.INVNO
        ORDER BY
            CASE WHEN E.EMLTP = 'AP' THEN 0
                 WHEN E.EMLTP = 'PO' THEN 1
                 ELSE 2 END) rn 
    FROM INVOICES I 
    LEFT JOIN CSEML E ON I.CUSTNO = E.CUSTNO
    WHERE I.INVNO = '10124'
) t
WHERE t.rn = 1

Answer

You may try using ROW_NUMBER here with a CASE expression to order/prioritize the email addresses of each invoice:

SELECT INVNO, CUSTNO, EMAIL
FROM
(
    SELECT I.INVNO, I.CUSTNO, E.EMAIL, ROW_NUMBER() OVER (PARTITION BY I.INVNO
        ORDER BY
            CASE WHEN E.EMAIL LIKE 'ap@%' THEN 0
                 WHEN E.EMAIL LIKE 'po@%' THEN 1
                 ELSE 2 END) rn
    FROM INVOICES I 
    LEFT JOIN CSEML E
        ON I.CUSTNO = E.CUSTNO
    WHERE I.INVNO = '10124'
) t
WHERE t.rn = 1;

Advertisement

Answer