SQL Select: choosing the right single record based on another field

Question

How to filter the list of records to delete those that have the same field, based on choosing one with a minimum value in another field? Please note that this is not enough to just get the minimum value ... I need to have other fields from the same record.

I have a “products” table, and I'm trying to add the ability to apply a coupon code. Due to how invoices are created, selling a product at different cost is considered another product. In the database you can see this:

Product ID, Product Cost, Product Name, Coupon Code
    1,          20,          Product1,     null
    2,          10,          Product1,    COUPON1
    3,          40,          Product2,     null

I have a query that now selects a list of all available products (based on other criteria, I simplify this). The problem is that for the above case, my query returns:

  1 - Product1 for $20
  2 - Product1 for $10
  3 - Product2 for $40

( , ), , . :

  2 - Product1 for $10
  3 - Product2 for $40

i.e., .

, MySQL, SQL.

Answer 1

:

SELECT T2.* 
FROM
(
    SELECT `Product Name` AS name, MIN(`Product Cost`) AS cost
    FROM products
    GROUP BY `Product Name`
) T1
JOIN products T2
ON T1.name = T2.`Product Name`
AND T1.cost = T2.`Product Cost`

, , :

SELECT CONCAT(`Product ID`, ' - ', T1.name, ' for $', T1.cost)