CODEWITHSUNDEEP
sqlms-access
TesteroniPeperoni
TesteroniPeperoni

Reputation: 21

MS Access Selecting distinct rows

I have table similar to this:

ID ProductName Price
1  Water       0.89
1  Water       0.99
1  Water       0.79
2  Coke        1.99
3  Sprite      1.99

What I would like is to get is the lowest price of every product. ( ID can't change for same name ) If I could group just by one column it would be fine but I can't since Access doesn't let me. My current code that I've been trying to deal with is:

SELECT DISTINCT Products.ProductName, Products.Price
FROM Products
GROUP BY Products.ProductName, Products.Price

Information that I would like to get should look like:

ProductName Price
Water       0.79
Coke        1.99
Sprite      1.99

Upvotes: 0

Views: 40

Answers (3)

user6256515
user6256515

Reputation: 102

SELECT P.ProductName, 
       MIN(P.Price) as Price
FROM Products p
GROUP BY P.ProductName

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5432

You are very close to it. Just use min() function:

SELECT Products.ProductName, min(Products.Price)
FROM Products
GROUP BY Products.ProductName;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Just use an aggregation on the product name (or id):

SELECT Products.ProductName, MIN(Products.Price) as Price
FROM Products
GROUP BY Products.ProductName;

Upvotes: 1

Related Questions