Finding best record in sub query

  • Thread starter Thread starter Frank M.
  • Start date Start date
F

Frank M.

How can I find the best record in a subquery. Let's a
simple example say that I have two tables:
Cust with CustID,
Order With CustID, OrderID, Product, Amount, Total, Date

Now, in my query I want for each customer to find the
orderID for best order (i.e. highest amount). I thought
something like this would work:

SELECT
Cust.CustID,
(SELECT TOP 1 OrderID FROM Order
WHERE Order.CustID = Cust.CustID
ORDER BY Order.Amount DESC)
FROM Cust
WHERE EXISTS (SELECT Order.OrderID FROM Order
WHERE Order.CustID = Cust.CustID)

This works if there is only one order. But in some cases
I get the error message "This sub query returns maximum a
single record". My guess is that the error occurs when
there is more than one order. Apparently TOP 1 makes sure
that only 1 order is returned. If there are 2 or more,
perhaps the ORDER BY should go into effect, however only
one record is returned due to TOP 1 and so the error
condition occurs, I guess - but I really don't know.

Is there any way that I can get the above to work? or
another way to find the best record in the sub query? I
have thought about using aggregation (MAX on the Amount
field), but that will only return the maximum amount - I
need the key (OrderID) for the sub record.

Help will be much appreciated.



With kind regards,


Frank M.
 
Back
Top