Optimize complicated query

Question

I have a query that merge table by some id.

SELECT ROW_NUMBER() OVER (ORDER BY PagedOptionData1.ExternalId ASC,
                                   PagedOptionData1.RunDateTime DESC
                          ) AS RowNumber,
       PagedOptionData1.*,
       PagedOptionData2.*
FROM   PagedOptionData AS PagedOptionData1
       LEFT OUTER JOIN PagedOptionData AS PagedOptionData2
         ON PagedOptionData1.ExternalId = PagedOptionData2.ExternalId
            AND PagedOptionData2.rn = 2
WHERE  PagedOptionData1.rn = 1  

Where PagedOptionData is a temp table with various join .

Can this query can be optimized ?

Answer 1

You need an index on (ExternalId ASC, RunDateTime DESC) whether you use your original form or my suggestion

One thing stands out: how will you remove ambiguity in column names when you use SELECT * twice on the same table?

To avoid ordinal column access and simplify the query, I'd consider re-writing:

SELECT TOP 2
   *
FROM
   PagedOptionData
ORDER BY
   ExternalId ASC, RunDateTime DESC

This simplifies matters considerably at the expense of more logic in the client code