How to avoid sort operator in a select query

Question

In my select statement I have simple sub-query that grabbing last ExpirationDate within ControlNo.

This subquery significantly slows down the performance. QuoteID is Clustered index on table tblQuotes

Statistics are up to date.

SELECT
  ControlNo,
  PolicyNumber,
(     
  SELECT TOP 1 Q.ExpirationDate
  FROM tblQuotes Q 
  WHERE Q.ControlNo = tblQuotes.ControlNo
  ORDER BY Q.QuoteID DESC
 )
SUM(Premium) as Premium
FROM tblQuotes
GROUP BY ...

Is it possible to find a workaround in this case?

Answer 1

Try replacing the subquery:

(     
  SELECT TOP 1 Q.ExpirationDate
  FROM tblQuotes Q 
  WHERE Q.ControlNo = tblQuotes.ControlNo
  ORDER BY Q.QuoteID DESC
 )

With a windows function if you are looking for maximum value

MAX(ExpirationDate) OVER(PARTITION BY ControlNo)

If you are looking for the first value in a specific order then use:

FIRST_VALUE(ExpirationDate) OVER(PARTITION BY ControlNo ORDER BY QuoteID DESC)