sql - Select columns with and without group by -

sql - Select columns with and without group by -

having table1

id | productname | store | cost ----------------------------------- 1 | name | store 1 | 4 2 | name | store 2 | 3 3 | name b | store 3 | 6 4 | name | store 3 | 4 5 | name b | store 1 | 7 6 | name | store 4 | 5 7 | name c | store 3 | 2 8 | name b | store 6 | 5 9 | name c | store 2 | 1

i need columns rows lowest price. result needed:

id | productname | store | cost ----------------------------------- 2 | name | store 2 | 3 8 | name b | store 6 | 5 9 | name c | store 2 | 1

my best seek is:

select productname, min(price) minprice table1 grouping productname

but need id , store each row.

try this

select p.* table1 p inner bring together (select productname, min(price) minprice table1 grouping productname) t on p.productname = t.productname , p.price = t.minprice

sql