Check if a certain Row existis, else select different row

Question

lets say I have a table containing

|vendor  | price| productID|  
|--------------------------------|  
|abc   | 6    | 0001     |     
|1     | 7    | 0001     |         
|def   | 8    | 0001     |      
|xyz   | 30   | 0002     |    
|zxy   | 32   | 0002     |

now I want to get the vendor which has the min() price for a product
for product 0001 that would be Vendor abc
for product 0002 that would be Vendor xyz

BUT! IF there is a Vendor named 1 I would like to see his name instead of the actual vendor with the min() price, if there is no Vendor named 1 for a product, I want to see the the one with the min() price again

if that makes any sense for you.. its kinda like a if-else construct but I dont know how to do it in SQL

(sorry for the bad formatted table, I just dont get it formatted the right way)

Thank you

Actually after re-reading a couple more times I am not sure. Did you mean for product = 0001, the vendor should be 1, but with what price? The price of 7 which is vendor 1's price, or still with the min() price of 6 (which is offered by a different vendor, not by vendor 1)? — user5683823
– user5683823, Commented Sep 1, 2016 at 13:46

Gordon Linoff · Accepted Answer · 2016-09-01 10:25:37Z

5

This is a prioritization query. One method is to use row_number() and to put the rules for prioritization into the order by. This resulting query:

select t.*
from (select t.*,
             row_number() over (partition by productId
                                order by (case when vendorid = 1 then 1 else 2 end),
                                         price asc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

answered Sep 1, 2016 at 10:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5683823 Over a year ago

This query does not meet the requirement. The requirement was to ALWAYS show the LOWEST price for a product. However, if vendor 1 sells that product, show 1 as the vendor NAME. This sounds like a "will match the lowest competitor price" arrangement. Also, as a separate matter, the vendor should be character type, and the case condition should use '1' (don't rely on implicit conversion from number to varchar2).

user5683823 · Accepted Answer · 2016-09-01 13:41:25Z

0

The (currently) accepted answer does not meet the OP's requirement. The requirement was to show the minimum price for each product, even if vendor '1' does not have the lowest price. However, when someone else has the lowest price but vendor '1' also sells the same product, replace the vendor name with '1' (but don't change the lowest price to vendor `1``s price). This looks like "will meet my competitors' lowest price" type of arrangement.

Query and output:

with 
     price_table ( vendor, price, productid ) as (  
       select 'abc', 6  , '0001' from dual union all     
       select '1'  , 7  , '0001' from dual union all          
       select 'def', 8  , '0001' from dual union all 
       select 'xyz', 30 , '0002' from dual union all 
       select 'zxy', 32 , '0002' from dual  
     ),
     prep ( vendor, price, productid, rn, ct ) as (
       select vendor, price, productid,
              row_number() over (partition by productid order by price),
              count( case when vendor = '1' then 1 end ) over ( partition by productid)
       from   price_table
     )
select case when ct = 0 then vendor else '1' end as vendor,
       price,
       productid
from   prep
where  rn = 1
;

VENDOR   PRICE PROD
------ ------- ----
1            6 0001
xyz         30 0002

answered Sep 1, 2016 at 13:41

user5683823

1 Comment

b101 Over a year ago

hi mathguy i didnt mean to replace the name of the vendor. i meant to say, that I need to see the minimum price and vendor name for a product. but if for a product, the lowest price is not by vendor 1, but vendor 1 is also selling it (for a higher price) I would like to see his price and name instead of the acutal lowest vendor. so basically I always want to see the vendor 1's price, no matter if he is the lowest. but there will be products which he doesnt sell, then I DO want to see the actual lowest seller and his price... sorry for my bad english

Collectives™ on Stack Overflow

Check if a certain Row existis, else select different row

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related