Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Currently I'm using:

SELECT t.CustomerLastName
FROM (
    SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName
    FROM OrderLine, Product, Orders, Customer
    WHERE OrderLine.ProductId = Product.Id
    AND Orders.Id = OrderLine.OrderId
    AND Customer.Id = Orders.CustomerId
    GROUP BY Orders.Id, Customer.Lastname
    ) AS t
WHERE t.OrderLinePrice = 
(
    SELECT MAX(s.OrderLinePrice) AS MaxOrderPrice
    FROM (
        SELECT SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, Orders.Id, Customer.Lastname AS CustomerLastName
        FROM OrderLine, Product, Orders, Customer
        WHERE OrderLine.ProductId = Product.Id
        AND Orders.Id = OrderLine.OrderId
        AND Customer.Id = Orders.CustomerId
        GROUP BY Orders.Id, Customer.Lastname
        ) AS s
)
ORDER BY CustomerLastName

As my query to fetch the list of names for which the price of their order is equal to the price of the most expensive order. Now having 2x more or less the same subquery in one query seems horribly inefficient, so I'm wondering if, how and where to start with optimising this. Any tips?

share|improve this question
add comment

1 Answer 1

You could try to use top (1) with ties.

SELECT TOP (1) WITH TIES 
  SUM(OrderLine.Amount * Product.Price) AS OrderLinePrice, 
  Orders.Id, 
  Customer.Lastname AS CustomerLastName
FROM OrderLine, Product, Orders, Customer
WHERE OrderLine.ProductId = Product.Id
  AND Orders.Id = OrderLine.OrderId
  AND Customer.Id = Orders.CustomerId
GROUP BY Orders.Id, Customer.Lastname
ORDER BY OrderLinePrice DESC
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.