Geek asks and answers

Select one of the lines with the condition

I have this sql table and rows defined in SQL Fiddle

The SUPPLIER_DETAILS table has a field named IS_PAYABLE that will be either null or "Y".

If IS_PAYABLE='Y', for each PRODUCT_REG there can be one or more records with different PRODUCT_NO . For instance. PRODUCT_REG = 'HP_C20'has two entries 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 for one PRODUCT_REG there are several records, then I would like only one of the records, and I need all the records with IS_PAYABLE is null.

How can i achieve this? If I do not make my requirement clear, I will explain further.

Any help is very noticeable.

thank

+5
source share
4 answers
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 an internal query, I rated products with the same product_reg.

In an external request, I have only one product per product_reg (first rating) and all non-payable products.

+5
source

Try the following:

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 (merging 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

+3
source

row_number() .

   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

№ 1

, . , , .

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

№ 2

+3

UNION ALL , where IS_PAYABLE = 'Y' , MAX() MIN()

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

+2
source

More articles:


All Articles