Rewriting a query to be more efficient

Question

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?

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