TesteroniPeperoni TesteroniPeperoni - 2 years ago 103
SQL Question

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

Answer Source

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

SELECT Products.ProductName, MIN(Products.Price) as Price
FROM Products
GROUP BY Products.ProductName;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download