I would like to find the price of the smallest product in a store and in addition, in another column, populate this price in all the products of the same store. Here is my table and the desired result in the “results” column:
Here is my request but it does not populate the price:
SELECT local ,product ,price ,IIF(MIN(Product) OVER (PARTITION BY Local)=Product,Price,NULL) as Results FROM Table1
Thanks
Advertisement
Answer
If you want price of the smallest product:
SELECT local, product, price, MAX(CASE WHEN product=MinProd THEN price ELSE 0 END) OVER (PARTITION BY Local)Results FROM ( SELECT local, product, price, min(product) OVER (PARTITION BY Local) as MinProd FROM Table1 ) X