Selecting a Column Which has MAX in Another

  • Thread starter Thread starter Smartin
  • Start date Start date
S

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.
 
Smartin said:
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).
Click to expand...

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
 
Amy said:
Smartin said:
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).
Click to expand...

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
Click to expand...

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!
 
Back
Top