SQL query top 10 orders per provider

What I am trying to get is each providers top 10 diagnosis/orders so each provider should show 10 or close to it,  diagnosis codes and how many times they ordered each of those during the year. I would expect to see their provider_id 10 times in a row with each diagnosis codes being used less than the one above it.
An example is below, if I use this I get one provider and all their top 10 diagnoses codes in order. All I'm trying to do is get this same thing but for all providers. The code below with a provider_id added gives the results shown on the attached Excel spreadsheet.
Your query was close but did not seem to return anything that gave a per provider top 10. Thanks

select TOP 10 e.icd9cm_code_id, e.icd9cm_code_desc, pm.provider_id, COUNT(e.icd9cm_code_id) as times_used
FROM encounter_diags e
inner join patient_encounter pe on e.enc_id = pe.enc_id
inner join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
WHERE pe.enc_timestamp >= '20150101' AND pe.enc_timestamp  < '20160101'
and pm.provider_id = '01A6E951-730F-4ECB-901E-BB4380EEE233'
GROUP BY pm.provider_id, e.icd9cm_code_id, e.icd9cm_code_desc 
ORDER BY COUNT(icd9cm_code_id) DESC

Select allOpen in new window

diagnosis_per_provider.xlsx

Missed out one thing in my query try the below one

with cte as (
select e.icd9cm_code_id, e.icd9cm_code_desc, pm.provider_id, COUNT(e.icd9cm_code_id) as times_used
FROM encounter_diags e
inner join patient_encounter pe on e.enc_id = pe.enc_id
inner join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
WHERE pe.enc_timestamp >= '20150101' AND pe.enc_timestamp  < '20160101'
--and pm.provider_id = '01A6E951-730F-4ECB-901E-BB4380EEE233'
GROUP BY pm.provider_id, e.icd9cm_code_id, e.icd9cm_code_desc 
),cte2 as (
Select row_number() over(partition by provider_id order by times_used desc) as rn,* from cte)
Select * from cte2 where rn<=10

Select allOpen in new window

Unfortunately this really didn't change anything. The format I need is in the example spreadsheet I attached. It needs to be broken down by provider_id and should return 10 rows per provider_id. The query I added does just that, but unfortunately I can only figure out how to do it for one provider at a time. thanks

What happens if you change this -
Select row_number() over(partition by provider_id order by times_used desc) as rn,* from cte)
to this -
Select row_number() over(partition by provider_id, icd90cm_code_id order by times_used desc) as rn,* from cte)

Closer but that didn't restrict it to top 10 per provider_id I got 24 in some cases. Thanks

Can you post the script that didn't restrict the output to 10 records per provider?

I need it to order by provider_id then by times used if possible.

with cte as (
select e.icd9cm_code_id, e.icd9cm_code_desc, pm.provider_id, COUNT(e.icd9cm_code_id) as times_used
FROM encounter_diags e
inner join patient_encounter pe on e.enc_id = pe.enc_id
inner join provider_mstr pm on pe.rendering_provider_id = pm.provider_id
WHERE pe.enc_timestamp >= '20150101' AND pe.enc_timestamp  < '20160101'
--and pm.provider_id = '01A6E951-730F-4ECB-901E-BB4380EEE233'
GROUP BY pm.provider_id, e.icd9cm_code_id, e.icd9cm_code_desc 
),cte2 as (
Select row_number() over(partition by provider_id, icd9cm_code_id order by times_used desc) as rn,* from cte)
Select * from cte2 where rn<=10

Select allOpen in new window

Thanks

Thanks