Select either one of the row with condition

Question

I have this sql table and rows defined in SQL Fiddle

In SUPPLIER_DETAILS table there is a field called IS_PAYABLE which will have values either null or 'Y'.

If IS_PAYABLE='Y', there could be one or more records for each PRODUCT_REG with different PRODUCT_NO. E.g. PRODUCT_REG = 'HP_C20' has two records with IS_PAYABLE='Y'.

HP_C20  FR-A    GB-A128     Y
HP_C20  FR-A    GB-A098     Y

What I would like to have is if IS_PAYABLE='Y' and if there are multiple records for one PRODUCT_REG, then I would want only either one of the record and I want all the records with IS_PAYABLE is null.

How can I achieve this? If I did not make my requirement understandable, I will explain further.

Any help is highly appreciable.

Thanks

Answer 1

select * from 
   (select a.*, 
         row_number() over (partition by product_reg order by product_no) as rnk 
   from SUPPLIER_DETAILS a
   order by PRODUCT_REG)
where is_payable is null or rnk = 1;

SQLFIDDLE

In inner query I've ranked the products with the same product_reg.

In the outer query i've got only one product per product_reg(the first ranked) and all non payable products.

Answer 2

Try this:

SELECT s.* 
FROM   supplier_details s 
WHERE  NOT EXISTS(SELECT s1.* 
                  FROM   supplier_details s1 
                  WHERE  s.is_payable = 'Y' 
                         AND s1.is_payable = 'Y' 
                         AND s.product_reg = s1.product_reg 
                         AND s.product_no < s1.product_no) 
UNION 
SELECT * 
FROM   supplier_details 
WHERE  is_payable IS NULL 

http://sqlfiddle.com/#!4/25c52/2/0

EDIT: below code should also work(union is redundant)

SELECT s.* 
FROM   supplier_details s 
WHERE  NOT EXISTS(SELECT s1.* 
                  FROM   supplier_details s1 
                  WHERE  s.is_payable = 'Y' 
                         AND s1.is_payable = 'Y' 
                         AND s.product_reg = s1.product_reg 
                         AND s.product_no < s1.product_no) 

http://sqlfiddle.com/#!4/5e69b/1/0