CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10g
Jacob
Jacob

Reputation: 14741

Select either one of the row with condition

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

Upvotes: 5

Views: 4721

Answers (4)

Nick Krasnov
Nick Krasnov

Reputation: 27251

In this situation row_number() analytical function is going to be in handy.

   select product_reg
        , product_supplier_code
        , product_no
        , is_payable
     from (select t.*
                , row_number() over (partition by product_reg order by product_no) m
             from SUPPLIER_DETAILS t
          )
    where m = 1
       or is_payable is null
    order by product_reg

Demo#1

In addition

Seems to be a duplicate. To distinguish the answer a little bit here is another approach you can use to get the desired result

select product_reg
    , max(PRODUCT_SUPPLIER_CODE) KEEP (DENSE_RANK FIRST ORDER BY product_reg) PRODUCT_SUPPLIER_CODE
    , max(PRODUCT_NO) KEEP (DENSE_RANK FIRST ORDER BY product_reg) PRODUCT_NO
    , max(IS_PAYABLE) KEEP (DENSE_RANK FIRST ORDER BY product_reg) IS_PAYABLE
from SUPPLIER_DETAILS t
group by product_reg 
order by product_reg

Demo#2

Upvotes: 3

Taryn
Taryn

Reputation: 247850

You can use UNION ALL for this, but when returning the records where IS_PAYABLE = 'Y' use an aggregate to always return the MAX() or MIN() value 

select PRODUCT_REG, PRODUCT_SUPPLIER_CODE, max(PRODUCT_NO) PRODUCT_NO, IS_PAYABLE
from SUPPLIER_DETAILS
where IS_PAYABLE = 'Y'
group by  PRODUCT_REG, PRODUCT_SUPPLIER_CODE, IS_PAYABLE
union all 
select PRODUCT_REG, PRODUCT_SUPPLIER_CODE, PRODUCT_NO, IS_PAYABLE
from SUPPLIER_DETAILS
where IS_PAYABLE is null
order by PRODUCT_REG

See SQL Fiddle with Demo

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

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.

Upvotes: 5

Ankur
Ankur

Reputation: 12774

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

Upvotes: 3

Related Questions