CODEWITHSUNDEEP
sqlmysqlnavicat
Eltar
Eltar

Reputation: 41

Get min googlePrice for each productId

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!

Upvotes: -1

Views: 39

Answers (1)

Isolated
Isolated

Reputation: 6454

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().

Upvotes: 0

Related Questions