Get min googlePrice for each productId

Question

I have got next SQL query

select * from ( select * from prices as p order by p.productId, p.googlePrice ) as t

It returns next result

Result of the query

I want to get the same table for each productId with min googlePrices

enter image description here

I have tried to use group by, having... something like this:

select t.productId, MIN(t.googlePrice), t.priceRaw, t.id, t.title from prices t group by t.productId

But it isn't a result that I need.

I need a table with records for each productId min googlePrice. I've marked records that I need with green line.

Result that I need

I can't solve the task. Help please!

Answer 1

You can use a windows function, partitioning by productId. Based on your question, not sure if you want a new column with the min_price, or rather, just the records with the min price. Both options below.

create table prices (
  id integer, 
  productId integer, 
  title varchar(20), 
  googlePrice integer
  );
  
insert into prices values 
(1, 22222, 'ABC', 20), 
(2, 22222, 'CDE', 50), 
(3, 22222, 'ZZZZ', 10), 
(4, 888, 'DDDD', 5);

---

Query #1 (if you just want another column and display all rows)

select *,
 min(googlePrice) over (partition by productId) as min_price
from prices;

id	productId	title	googlePrice	min_price
4	888	DDDD	5	5
1	22222	ABC	20	10
2	22222	CDE	50	10
3	22222	ZZZZ	10	10

---

Query #2 (if you want to display only the min rows)

select *
from (
 select *,
  row_number() over (partition by productId order by googlePrice) as mp
 from prices
  )z
where mp = 1;

id	productId	title	googlePrice	mp
4	888	DDDD	5	1
3	22222	ZZZZ	10	1

---

View on DB Fiddle

UPDATE If you have ties for lowest price by productId, and want to display ALL with the lowest value, then use RANK() instead of ROW_NUMBER().