Alvin, I don't OWN a banjo. 😉

I do need to come up with another verse or two, though.

I just did.

Ahahahahahahahaaaaaaaaaaaaaaa............. 😀

Forgive the cruel cackle of evil amusement.

Lynn, I usually make it a point to avoid looking at any questions with his name on it. It preserves the serenity of my own personal desert....

And already answered.....

Lynn, do you realize we're almost at 5k elsewhere?

Hi.

How's it going?

😀

But, as I said in the original post, if I run this proc as a job within SQLServer Enterprise Manager (v8), then it always fails with the following message...

Executed as...

Very nice little summary, John. Thank you.

Shhhhhh.... 😉

The imprecision was what troubled me. But it makes sense if you want to keep "ties". I assume that the optimizer has to do a...

Mr. Wilbur said RANK()

Arrrgg! Flo, I just realized I never proofed your article!

I probably shouldn't have thrown the CROSS_APPLY suggestion in. Row_Number() with partitioning is quite quick for...

Now how could we do this? ...

TomYum,

If you are going to continue to code in SQL, you need to very quickly read up on JOIN in all it's forms. It's absolutely essential. ...

Bruce, how does the RANK approach differ from ROWNUMBER()?

Also, I don't believe that CROSS APPLY will always do a subquery per row. The optimizer may surprise you.

Or maybe...

;with distinctObjTypes as

(SELECT DISTINCT objtype FROM sys.dm_exec_cached_plans)

--

SELECT d.objtype,ca.*

FROM distinctObjTypes d

CROSS APPLY

(SELECT top 10 usecounts, cacheobjtype, plan_handle

FROM sys.dm_exec_cached_plans

WHERE ca.objtype = d.objtype

ORDER BY USECOUNTS DESC) ca

--