Suppose you have a table of products. Each product has a price and a discount. Like

Does anybody know how to select the lowest price (first) and at the highest discount (second)? Like

I have to use MS Access for my query and I tried (http://sqlfiddle.com/#!18/bcf208/1)

CREATE TABLE Product
    ([productName] varchar(5), [price] INT, [discount] INT, [region] varchar(5))
;

INSERT INTO Product
    ([productName], [price], [discount], [region])
VALUES
    ('A', 11,5, 'US'),
    ('A', 10,4, 'EU'),
    ('A', 10,1, 'Asia'),
    ('D', 50,6, 'US')

SELECT A.*
FROM Product A
INNER JOIN (
SELECT top 1
  B.price,
  B.discount
FROM Product B
WHERE B.productName = A.productName 
ORDER BY B.price ASC, B.discount DESC
) B1 ON B1.price = A.price AND B1.discount = A.discount

-- Error: The multi-part identifier "A.productName" could not be bound.

CodePudding user response：

If I don't get you wrong, you should use the MIN and MAX functions, and you can use both in the same statement.

SELECT MIN(A.price), MAX(A.discount)
FROM Product A

CodePudding user response：

If I understand you correctly you are trying to find the lowest price for each product first, and then for that product name and price, what is the highest discount. This should do it.

SELECT A.ProductName,
    A.MinOfPrice,
    B.MaxOfDiscount
FROM (SELECT P.productName,
        MIN(P.price) AS MinOfprice
    FROM Product as P
    GROUP BY P.productName)
    AS A
INNER JOIN
    (SELECT P.productName,
        P.Price,
        MAX(P.Discount) AS MaxOfDiscount
    FROM Product as P
    GROUP BY P.productName,
        P.Price)
    AS B
ON  A.ProductName = B.ProductName AND
    A.MinOfPrice = B.Price