Need to fetch a row with minimum value in postgreSQL

Question

I need to fetch a complete row where price will be minimum. Here price will calculated within the query from special price along with its date. In case, if the price is same for 2 rows with different place_id then it should fetch anyone row.

Look at the below structure which is used so far.

Consider stock of the all row are "1";

Price Table:

product_id  | place_id | price    | special_price | special_date_from   | special_date_to
--------------------------------------------------------------------------------------------
27          |27        |1000.0000 |0.0000         |                     |
26          |27        |500.0000  |129.0000       |2015-05-15 00:00:01  |2015-08-30 23:59:59
26          |24        |1500.0000 |0              |                     |
27          |5         |56224.0000|0              |                     |
27          |128       |1000.0000 |100.0000       |2015-07-31 00:00:01  |2015-08-12 23:59:59
27          |121       |100.0000  |0              |                     |
26          |121       |500.0000  |0              |                     |

My Query IS:

select * 
  from ( 
        (select min(price) price, 
                myt.product_id 
           from ( select (case when 
                              (cpp.special_price_fromdate <= '2015-08-04 19:18:49' 
                               and cpp.special_price_todate >= '2015-08-04 19:18:49' 
                               and cpp.special_price > 0) 
                               then cpp.special_price else cpp.price end
                          ) as price,  
                          cpp.product_id, 
                          cpp.place_id
                   from product_price as cpp 
                  where cpp.in_stock > 0   
                    and cpp.place_id IN (130,27,128,129,126,121,54)
                ) as myt group by product_id
        ) t1 
inner join
    (select DISTINCT on(pps.product_id) 
            (case when (pps.special_price_fromdate <= '2015-08-04 19:18:49' 
                        and pps.special_price_todate >= '2015-08-04 19:18:49' 
                        and pps.special_price > 0) 
                  then pps.special_price 
                  else pps.price end) as price, 
            pps.product_id,
            pps.price as old_price, 
            pps.place_id 
       from product_price pps 
      where pps.in_stock > 0
    ) t2 on t1.price = t2.price 
            and t1.product_id = t2.product_id 
            and t1.product_id in ('26','27')
) AS "pp";

I want the results to be:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000      
27          | 121      | 100.0000 | 100.0000

But i get the results based on the above query:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000

27 product_id has been skipped because of equal price which i have checked in "On Condition". I dont know why :(

Answer 1

select product_id, price_id, price from
(
select *, row_number() over(partition by product_id order by price,place_id) as rn
from product_price
)
where rn = 1;

You can use the row_number function to number the rows starting with 1 for the lowest price in each partition. Add more calculations as necessary for other columns.