Selecting a Column Which has MAX in Another

[Smartin]

Good Day All,

I will use the Northwind table "Order Details" as an example for what I
am trying to do.

I would like to obtain, for each order, the product ID which has the
highest unit cost.

I tried the simplest approach:

SELECT
[Order Details].OrderID,
First([Order Details].ProductID) AS FirstOfProductID,
Max([Order Details].UnitPrice) AS MaxOfUnitPrice
FROM
[Order Details]
GROUP BY [Order Details].OrderID;

This clearly doesn't work. It does return MAX(UnitPrice) correctly, but
does not always return the corresponding ProductID which has that price
(e.g. see the result for OrderID 10253).

I'm not concerned about ties where two products have the same price as
in my real life problem this does not matter.

If anyone as SQL 101 pointers for me I would be much appreciative.

 

[Amy Blankenship]

Good Day All,

I will use the Northwind table "Order Details" as an example for what I am
trying to do.

I would like to obtain, for each order, the product ID which has the
highest unit cost.

I tried the simplest approach:

SELECT
[Order Details].OrderID,
First([Order Details].ProductID) AS FirstOfProductID,
Max([Order Details].UnitPrice) AS MaxOfUnitPrice
FROM
[Order Details]
GROUP BY [Order Details].OrderID;

This clearly doesn't work. It does return MAX(UnitPrice) correctly, but
does not always return the corresponding ProductID which has that price
(e.g. see the result for OrderID 10253).

It is actually working. It's retrieving the first ProductID in an order and
the highest UnitPrice in that order. That's what you asked for.

If you want the Product ID that has the maximum unit price in every order,
probably the easiest way if you're just starting out is

1) First, create a query that retrieves the max unit price for each order
SELECT
OrderID, Max(UnitPrice) AS TopPrice
FROM
Order Details
GROUP BY
OrderID

2) Next, join that query back to the table and get the first ProductID
where the unit price is the same as the top price
a) press the new button in the query window and select design mode
b) show your order details table and the query above when prompted
c) click on TopPrice and drag it to UnitPrice to create a join
d) drag OrderID, UnitPrice, and ProductID down from Order Details
e) click the Totals button. change Group By on ProductID to First

HTH;

Amy

 

[Smartin]

Good Day All,

I will use the Northwind table "Order Details" as an example for what I am
trying to do.

I would like to obtain, for each order, the product ID which has the
highest unit cost.

I tried the simplest approach:

SELECT
[Order Details].OrderID,
First([Order Details].ProductID) AS FirstOfProductID,
Max([Order Details].UnitPrice) AS MaxOfUnitPrice
FROM
[Order Details]
GROUP BY [Order Details].OrderID;

This clearly doesn't work. It does return MAX(UnitPrice) correctly, but
does not always return the corresponding ProductID which has that price
(e.g. see the result for OrderID 10253).

It is actually working. It's retrieving the first ProductID in an order and
the highest UnitPrice in that order. That's what you asked for.

If you want the Product ID that has the maximum unit price in every order,
probably the easiest way if you're just starting out is

1) First, create a query that retrieves the max unit price for each order
SELECT
OrderID, Max(UnitPrice) AS TopPrice
FROM
Order Details
GROUP BY
OrderID

2) Next, join that query back to the table and get the first ProductID
where the unit price is the same as the top price
a) press the new button in the query window and select design mode
b) show your order details table and the query above when prompted
c) click on TopPrice and drag it to UnitPrice to create a join
d) drag OrderID, UnitPrice, and ProductID down from Order Details
e) click the Totals button. change Group By on ProductID to First

HTH;

Amy

FWIW I found it is necessary to include an additional join between
TopPrices.OrderId = [Order Details].OrderID for this to work properly.
Otherwise, multiple orders are returned in the final product.

Thanks for your suggestion!