0

So, I have a table and I wish to display the row with MIN price... 

sellers - saleid, productsid[fk for products table], cid[fk for customers table], quantity
price.

Here are a few records: 

sellers =1,2,2,200,5.00
sellers=2,3,4,100,1.00

I do this query: 

select ProductID, Quantity, MIN(Price) FROM sellers

and I get this output

2,200,1.00

Why does it show the min Price however the first record column? It should display i.e. the corresponding row...

3,4,1.00

Any idea why this is?

=========EDIT======= Thanks for the suggestion guys it works. I have now another small problem. I wish to select the MIN price for each product. Is there a query I can use to do this? So, if for example this is the new sellers table:

sellers =1,2,2,200,5.00
sellers=2,3,4,100,1.00
seller=3,2,3,250,3.00

then the min price for products 2,3 would be 

sellers=2,3,4,100,1.00
seller=3,2,3,250,3.00

How can I do something like this using the min function? I have tried

select c.Fname p.ProductName, s.ProductID, s.Quantity, s.Price 
FROM sellers s, products p, customer c
WHERE s.Price = (select MIN(Price) FROM sellers WHERE p.ID=s.ProductID AND c.ID=s.cid);

This however does not seem to output the min price per unique product. Any ideas as to how I can rectify this?

Thanks

Improve this question
1
  • You should get more consistent results when using aggregate functions (e.g. MIN()) when you also include the non-aggregated columns in GROUP BY clause. Commented Feb 12, 2013 at 23:39

3 Answers 3

Reset to default
1

Without a GROUP BY the aggregate function MIN() gets applied to all rows. In order to compare a price against the minimum value across the whole table, you need a subquery:

select ProductID, Quantity, Price 
FROM sellers
WHERE Price = (select MIN(Price) FROM sellers);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I have a small problem now. I have a products table with the product ID, the sellers table as listed, customer's table and a requests table. I want to output the lowest price supplier for a particular product I am running this query but it dont seem to output the lowest price for the specified product just the lowest one in the table ...here is the query: select c.FName, p.ProductName, s.Price FROM customers c, products p, sellers s, requests r WHERE s.Price = (select MIN(Price) From sellers WHERE s.ProductID= r.ProductID AND c.ID=s.C_ID AND p.ProductID=s.ProductID)
I want it so that it displays the cheapest price seller where the request.ProductID = sellers.ProductID... i.e. shows the cheapest price product as requested by the user... NOT the cheapest price product in the sellers table which is what is happening at present. How could I modify this query ? Thanks
0

It's because you're selecting, without aggregation, non-aggregated columns in an (implicitly) aggregate query. In fact, I'm surprised the query is valid at all.

What I mean by that is, if you have aggregation in the values returned (such as min(Price) or avg(Price)), the query shouldn't be considered valid if there is any ambiguity about what other non-aggregated values (such as ProductID) should return. You might think oh, but there's only going to be one row with the minimum price so it can return the values from those. But what about other aggregations like avg that can produce a value contained in no now? Or what if two rows had the same minimum price? What row's values would you expect select ProductID, Quantity, AVG(Price) FROM sellers to return?

You want something like:

select ProductID, Quantity, Price from sellers s1 where Price <= all (select price from sellers s2)

Or

select ProductID, Quantity, Price from sellers s1 order by price asc limit 1

Improve this answer

2 Comments

@johnny As you can see from all the answers, there's a hundred different ways to write an SQL query to get the same result :)
I have ran into another small problem. I have asked the question here: [link]stackoverflow.com/questions/14912289/… would you know how to help me on something like this please?
0

The problem is you are not using a GROUP BY and you want to columns that are not in an aggregate function.

If you do not use a GROUP BY on the on the other columns, then MySQL picks the value for the ProductId, Quantity, etc and the result will be inconsistent.

You best bet is to use a subquery:

select *
from sellers s1
inner join
(
  select min(price) MinPrice
  from sellers
) s2
  on s1.price = s2.minprice;

See SQL Fiddle with Demo

Improve this answer

3 Comments

I have a small problem now. I have a products table with the product ID, the sellers table as listed, customer's table and a requests table. I want to output the lowest price supplier for a particular product I am running this query but it dont seem to output the lowest price for the specified product just the lowest one in the table ...here is the query: select c.FName, p.ProductName, s.Price FROM customers c, products p, sellers s, requests r WHERE s.Price = (select MIN(Price) From sellers WHERE s.ProductID= r.ProductID AND c.ID=s.C_ID AND p.ProductID=s.ProductID)
I want it so that it displays the cheapest price seller where the request.ProductID = sellers.ProductID... i.e. shows the cheapest price product as requested by the user... NOT the cheapest price product in the sellers table which is what is happening at present. How could I modify this query ? Thanks
@johnny if you have another question then you should post a new question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.