skip to Main Content

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


  1. SELECT 'productName' 
    FROM Products 
    ORDER BY Price LIMIT 1
    

    thanks to @akina

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
  3. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search