KG47 KG47 - 4 months ago 13
SQL Question

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:


  1. If only 1st condition satisfies, then return the record with CUST_FLAG= 'P'

  2. If only 2nd condition satisfies, then return the record with CUST_FLAG= 'J'

  3. If both the conditions satisfies, then return only the record with CUST_FLAG= 'P'.


Answer

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;