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)

Answer 2

If the latest record contains the Latest ExpirationDate, then just use MAX operator similar to below:

  SELECT
    ControlNo,
    PolicyNumber,
    MAX(ExpirationDate) ExpirationDate,
    SUM(Premium) as Premium
  FROM tblQuotes
  GROUP BY ControlNo, PolicyNumber;

However, if the ExpirationDate is not always the latest and you just want to get the value of whatever the latest record is then I would suggest similar to below. Get the QouteID value first (assuming this is PK) before getting the ExpirationDate to minimize on sorting while seeking for the expirationdate value.

SELECT q.*, qx.ExpirationDate 
FROM 
  (
    SELECT ControlNo, PolicyNumber, SUM(Premium) as Premium
    FROM tblQuotes t
    GROUP BY ControlNo, PolicyNumber
  ) q
  OUTER APPLY
  (
    SELECT ExpirationDate
    FROM tblQuotes q2
    WHERE q2.QuoteID=(SELECT MAX(QouteID) MaxQouteID FROM tblQuotes q1 WHERE q1.ContolNo=q.ControlNo)
  ) qx;