I’m building a store procedure that returns the lowest price of each product and show their respective store info and date. This SP should allow optional parameters for StoreCity and ProdType. I’m a bit stuck at the SELECT query.

These are the tables:

`+-----------+
| Product   |
+-----------+
| id_prod   |
| prod_type |
+-----------+`

+-----------+ | Store | +-----------+ | idStore | | StoreName | | StoreCity | +-----------+

`+---------------+
| Product_Store |
+---------------+
| fkStore       |
| fkProd        |
| PriceProd     |
| PriceDate     |
+---------------+`

At first, I was trying this:

`SELECT fkProd, MIN(PriceProd)
FROM Product_Store
GROUP BY fkProd;`

to get this:

`+---------+---------------+
| fkProd  | MIN(PriceProd)|
+---------+---------------+
|       1 |          1.11 |
|       2 |          0.11 |
|       3 |          1.11 |
|       4 |          0.11 |
+---------+---------------+`

I’s close to what I expect, but I also need the fkStore and PriceDate. So I use the query below, but (as far as I can see) it won’t allow me to use parameters (they should be in the first SELECT within the WITH clause, right?)

WITH q AS ( SELECT fkStore, fkProd, PriceProd, row_number() over (partition by TabComb order by PriceProd ASC) MinPrice, PriceDate FROM Product_Store) SELECT Store.StoreName, Product.ProdType, PriceProd, PriceDate FROM q INNER JOIN Posto ON q.fkStore = Store.idStore INNER JOIN Combustivel ON q.fkProd = Product.idProd WHERE MinPrice = 1