Google Query Language - Adding column on a group By

Question

Excuse me this is my first post. I tried to make a nice table like others do but I struggled with the formatting Please consider the following spreadsheet:

table image

Google spreadsheet: https://docs.google.com/spreadsheets/d/1t9Jvn4cJ4PGdJrEg7oiC6UsY2WaJdSw-d15IWpp3lUg/edit#gid=0

what I would like to get is lower price and lowest price seller per product : Where I am

=query(datarange,select col(A),min(col2) group by col(A),-1)

I do not get how I can get add the lowest seller because I do add it then I need to group by it. By reading another post it seems arrayformula is the way to get but I do not get how I should proceed...

Answer 1

try:

=ARRAYFORMULA({"seller"; IFNA(VLOOKUP(F3:F&G3:G, {A:A&B:B, C:C}, 2, 0))})

---

or in F2:

=ARRAYFORMULA({QUERY(A1:C, 
 "select A,min(B) where C is not null group by A", 1), 
 ARRAY_CONSTRAIN({"seller"; IFNA(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY(A2:C,
 "select A,min(B) where C is not null group by A", 0), 
 "offset 1", 0)),,999^99)), {A2:A&" "&B2:B, C2:C}, 2, 0))}, COUNTA(QUERY(A1:C, 
 "select A,min(B) where C is not null group by A", 1)), 1)})

Answer 2

I like to do these using sort and sortn:

=sortn(sort(filter(A2:C,A2:A<>""),1,true,2,true),999,2,1,true)

The idea is that you sort so that the lowest price for each product comes first using sort, then drop the duplicates using sortn, leaving only the row with the lowest price for each product.