Hi Brian, why would you want to eliminate row_number() from the query? Simply copy and paste your code into the test script that SwePeso posted and see for yourself how your version compares to the alternatives already presented. Peso's suggestion has by far the least IO and cpu time; I've taken that and added a fix for a functional problem in all suggestions: the special-characters &, < and > that for xml translates into & amp ;, & lt ; and & gt ; respectively (I put some additional spaces in that aren't in the real codes because the forum software shows the complete codes as &, < and > again). Your example re-introduces that problem and performs much worse because it re-introduces distinct instead of the group by in a sub query. So is there any specific reason why you think your version should be used instead of mine?

edit: codes were not shown correctly by the forum software.