b101 b101 - 1 year ago 40
SQL Question

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

Answer Source

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;