When I execute the following query, the name of any product appears, but not the product with the lowest price:
SELECT MIN(Price), ProductName
FROM Products;
How could I see the name of the product with the lowest price?
Thanks in advance!
When I execute the following query, the name of any product appears, but not the product with the lowest price:
SELECT MIN(Price), ProductName
FROM Products;
How could I see the name of the product with the lowest price?
Thanks in advance!
3
Answers
thanks to @akina
Consider this query:
Which product should it show? How about this one:
There may not be any product whose price is exactly the average value. Which product should it shown then?
What if there are multiple products tied for the minimum price?
The underlying reason for these problems, and the one you described in your question, is that the query is ambiguous. You can’t write an SQL query that magically knows which product to show, because the query doesn’t logically describe that relationship between the columns.
This query is not legal SQL in most brands of SQL database. It’s only legal in MySQL if you use an outdated SQL mode that allows it. This SQL mode is disabled by default in modern versions of MySQL, so the query you show should return an error.
There are several solutions to get the result you want. Here are some examples:
See also my answer to Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
I also write about this topic in the chapter "Ambiguous Groups" in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.
or
or