mysql query to get the name of a product with the lowest price

Question

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!

Answer 1

Consider this query:

SELECT MIN(Price), MAX(Price), ProductName
FROM Products;

Which product should it show? How about this one:

SELECT AVG(Price), ProductName
FROM Products;

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:

SELECT Price, ProductName FROM Products ORDER BY Price LIMIT 1;

SELECT Price, ProductName
FROM (
  SELECT Price, ProductName, RANK() OVER (ORDER BY Price) AS rnk
  FROM Products
) AS t
WHERE rnk = 1;

SELECT p1.Price, p1.ProductName
FROM Products AS p1
LEFT OUTER JOIN Products AS p2
  ON p1.Price > p2.Price
WHERE p2.Price IS NULL;

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.

Answer 2

SELECT Price, ProductName FROM Products ORDER BY Price ASC LIMIT 1

or

SELECT Price, ProductName FROM Products  WHERE Price = 
(SELECT MIN(Price) FROM Products

or

SELECT
 Price, ProductName
FROM Products S inner join (select min(Price)  from Products) mn
  on S.Price = mn.Price

Answer 3

SELECT 'productName' 
FROM Products 
ORDER BY Price LIMIT 1

thanks to @akina