Selecting a Column Which has MAX in Another (Revisited)

[Smartin]

Good Day All,

I would like to revisit my question of 4/26. Although I did receive a
solution using MS Access' version of SQL, I would like to know if there
is a solution using non-proprietary (ANSI?) SQL.

There is a reason for this apparently difficult exercise: I need the
solution to be portable. I looked for over an hour at Usenet posts on
the same topic and found many helpful attempts at a solution, but no
successes.

I have to think this is possible. I would like to think the gurus among
you--and there are many--have come across this problem before and tried
to work out something besides the hack that MS makes possible. (Not that
I'm faulting anyone for using the hack, mind you! But the hack is not
always available.)

The quest is on... If I had a reward to offer, I would. What can I give?
A prominent spot on my website? A shower of thank-you's and birthday
cards for the rest of your life? Glowing references for your resume?
I'll do anything within reason; make me an offer...

So here is the question again, slightly revised, and with obfuscating
experimentation removed...

============

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'm not concerned about ties where two products have the same price as
in my real life problem this does not matter.

The result must use only standard SQL to ensure portability.

 

[Douglas J. Steele]

You can find out the highest unit cost per order using:

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

You can then join that back to the Order Details table to determine what
product it was:

SELECT [Order Details].OrderID,
[Order Details].ProductID,
[Order Details].UnitPrice,
[Order Details].Quantity,
[Order Details].Discount
FROM [Order Details] INNER JOIN
(SELECT [Order Details].OrderID,
Max([Order Details].UnitPrice) AS MaxOfUnitPrice
FROM [Order Details] GROUP BY [Order Details].OrderID) AS MP
ON [Order Details].OrderID = MP.OrderID AND [Order Details].UnitPrice =
MP.MaxOfUnitPrice

As far as I know, that's standard SQL.

 

[Smartin]

You can find out the highest unit cost per order using:

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

You can then join that back to the Order Details table to determine what
product it was:

SELECT [Order Details].OrderID,
[Order Details].ProductID,
[Order Details].UnitPrice,
[Order Details].Quantity,
[Order Details].Discount
FROM [Order Details] INNER JOIN
(SELECT [Order Details].OrderID,
Max([Order Details].UnitPrice) AS MaxOfUnitPrice
FROM [Order Details] GROUP BY [Order Details].OrderID) AS MP
ON [Order Details].OrderID = MP.OrderID AND [Order Details].UnitPrice =
MP.MaxOfUnitPrice

As far as I know, that's standard SQL.

Thanks Douglas, that's exactly what I was looking for. You're tops!