CODEWITHSUNDEEP
sql
user3701922
user3701922

Reputation: 13

Select columns with and without group by

Having Table1

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

I need to get all columns but only the rows with the lowest price.
Result needed:

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

My best try is: 

SELECT ProductName, MIN(Price) AS minPrice  
FROM Table1  
GROUP BY ProductName

But then I need the ID and STORE for each row.

Upvotes: 1

Views: 79

Answers (5)

Uriil
Uriil

Reputation: 12628

This should work for you:

SELECT * FROM `Table1` AS `t1`
WHERE (
   SELECT count(*) FROM `Table1` AS `t2` WHERE `t1`.`productName` = `t2`.`productName` AND `t2`.`price` < `t1`.`price`) < 1

Check SqlFiddle

But if you have same products with same minimum price in two stores, you will get both of them in result output

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

You didn't mention your SQL dialect, but most DBMSes support Standard SQL's "Windowed Aggregate Functions":

select *
from
  ( select t.*,
       RANK() OVER (PARTITION BY ProductName ORDER BY Price) as rnk 
    from table1 as t
  ) as dt
where rnk = 1

If multiple stores got the same lowest price all of them will be returned. If you want only a single shop you have to switch to ROW_NUMBER instead of RANK or add column(s) to the ORDER BY.

Upvotes: 1

Vinit Prajapati
Vinit Prajapati

Reputation: 1613

Try this

select p.* from  Table1 as p inner join 
(SELECT ProductName, MIN(Price) AS minPrice    FROM Table1      GROUP BY ProductName) t 
on p.productname  = t.ProductName and p.price = t.minPrice

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156978

I think this query should do:

select min(t.id) id
,      t.productname
,      t.price
from   table1 t
join
( select min(price) min_price
  ,      productname
  from   table1
  group
  by     productname
) v
on     v.productname = t.productname
and    v.price = t.min_price
group
by     t.productname
,      t.price

It determines the lowest price per product and fetches every line in the base table (t). This avoids duplicates by grouping on the productname and selecting the lowest id.

Upvotes: 0

Azar
Azar

Reputation: 1867

Select ID,ProductName,minPrice
from
(
SELECT ProductName, MIN(Price) AS minPrice
FROM Table1
GROUP BY ProductName
) t
join Table1 t1 on t.ProductName = t1.ProductName

Upvotes: 1

Related Questions