CODEWITHSUNDEEP
sqloracle-database
b101
b101

Reputation: 379

Check if a certain Row existis, else select different row

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

Upvotes: 1

Views: 49

Answers (2)

user5683823
user5683823

Reputation:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270863

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;

Upvotes: 5

Related Questions