Search code examples
sqlt-sqlwindow-functions

SQL- Find the price of the smallest product in a store

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:

Table1

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

Solution

  • 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