Q:

SQL Query for returning only 1 record with 2 conditions satisfied

 WHERE (ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'P')     -- 1
    OR (ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'J')     -- 2

Please help me with this piece of query. I need to show only the record with CUST_FLAG = 'P'. With the above Where clause I am getting both the records if both the conditions are satisfied.
My Requirement is:

If only 1st condition satisfies, then return the record with CUST_FLAG = 'P'
If only 2nd condition satisfies, then return the record with CUST_FLAG = 'J'
If both the conditions satisfies, then return only the record with CUST_FLAG = 'P'.

A:

This is a prioritization query.  To do this in a single where clause, you can do:
WHERE ADDR1 = '1500 Valley Rd' AND
      (CUST_FLAG = 'P' OR
       (CUST_FLAG = 'J' AND
        NOT EXISTS (SELECT 1 FROM t WHERE t.ADDR1 = outer.ADDR1 AND t.CUST_FLAG = 'J'
       ))

Or a more typical way is to use ROW_NUMBER():
select t.*
from (select t.*, row_number() over (partition by addr1 order by cust_flag desc) as seqnum
      from (<your query here>) t
     ) t
where seqnum = 1;

