CODEWITHSUNDEEP
sqloracle-databaseoracle12c
KG47
KG47

Reputation: 13

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'.

Upvotes: 1

Views: 1566

Answers (3)

wra
wra

Reputation: 169

You can achieve this by sorting the records and only taking the first one. (But note that ROWNUM might by Oracle-specific syntax.)

SELECT * FROM (
SELECT <column_list> 
FROM <table>
WHERE ((ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'P')     -- 1
    OR (ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'J'))     -- 2
ORDER BY CUST_FLAG DESC
) WHERE ROWNUM = 1

Since Oracle 12c, the following syntax is also possible:

SELECT <column_list> 
FROM <table>
WHERE ((ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'P')     -- 1
    OR (ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'J'))     -- 2
ORDER BY CUST_FLAG DESC
FETCH FIRST 1 ROWS ONLY;

See also: - On ROWNUM and Limiting Results - How do I limit the number of rows returned by an Oracle query after ordering?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 1

George Sharvadze
George Sharvadze

Reputation: 570

Try to play around case/when

case 
  when ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'P' then 'P'
  when ADDR1 = '1500 Valley Rd' AND CUST_FLAG = 'J' then 'J'
  else 'P' end

Upvotes: 0

Related Questions