Select Top 1 By Number of Records

Question

I need to select the top Provider for each Patient, based on the number of claims attributed to each Provider, or in other words, select the provider that the patient goes to most often. For instance, I have the following data:

| Patient  | Provider | Claim Number |
| 001      | 001      | 0001         |
| 001      | 002      | 0002         |
| 001      | 002      | 0003         |
| 002      | 001      | 0004         |
| 002      | 003      | 0005         |
| 002      | 003      | 0006         |

The desired results would show:

Patient  | Top Provider
001      | 001 
002      | 003

This is my current attempt:

SELECT pat.patient,clms.provider AS [Top Provider]
FROM PatientList pat
LEFT OUTER JOIN Claims clms
 ON  clms.Provider =(
    SELECT Provider FROM
        (
        SELECT TOP 1 Provider, COUNT(DISTINCT [Claim Number]) 
        FROM Claims
        WHERE ssn = pat.ssn
        GROUP BY Provider
        ORDER BY COUNT(DISTINCT [Claim Number]) desc
        )
    )

From your given examples, isnt Patient 001's top provider , provider 002? — Andrew, Oct 15 '18 at 15:18
Please tell me you are not using SSN as a foreign key between tables. — Sean Lange, Oct 15 '18 at 15:20
@SeanLange No, it's just for the example. It's too complicated to explain but there are compound natural keys. That part I can figure out if someone will give me the general answer. — Irena Rich, Oct 15 '18 at 15:21
Oh good because that data should be encrypted at all times and viewable by only a few people. :) — Sean Lange, Oct 15 '18 at 15:23
Incidentally, your query was pretty close to working. It's probably not the best way to write this query (personally I would use `ROW_NUMBER()`) but if you wanted to get it to work then I think it just needs an alias name adding after the second last parenthesis, e.g. add an `x` in between the last two `)`s. — Richard Hansell, Oct 15 '18 at 15:32

score 3 · Answer 1 · answered Oct 15 '18 at 15:28

3

What you need is 'OUTER APPLY'.

I will show you the general idea. I hope you can work it out with your data.

SELECT pat.patient,clms.provider AS [Top Provider]
FROM PatientList pat

OUTER APPLY (

        SELECT TOP 1 Provider, COUNT(DISTINCT [Claim Number]) 
        FROM CCLF5_PT_B_w_MBI
        WHERE ssn = pat.ssn
        AND
        clms.Provider=pat.Provider
        GROUP BY Provider
        ORDER BY COUNT(DISTINCT [Claim Number]) desc
        )clms

answered Oct 15 '18 at 15:28

MEdwin

2,940
1
14
27

You don't need OUTER APPLY as the assumption would be that each Patient has had at least 1 claim to be in the table. Using CROSS APPLY is more appropriate. SELECT DISTINCT p.Patient, t.TopProvider FROM dbo.t1 p CROSS APPLY ( SELECT TOP (1) Patient, Provider as TopProvider, COUNT(*) as NumVisits FROM dbo.t1 WHERE Patient = p.Patient GROUP BY Patient, Provider ORDER BY COUNT(*) DESC ) t – Martin Cairney Oct 15 '18 at 15:34
@MartinCairney, I agree if there are all cases in the sub. But that is not explicit in the question and even that case, the result will still be the same. Try it out. Also note that this is a desgn issue and I agree that speed can be gained by using CROSS APPLY. – MEdwin Oct 15 '18 at 15:37
Thanks, @MEdwin. I went with Row_Number for now but will try this if I run into issues. – Irena Rich Oct 15 '18 at 15:44

score 2 · Accepted Answer · edited Oct 15 '18 at 15:44

2

I would use row_number() :

with cte as (
     <your query goes here>
 )
select Patient, Provider
from (select c.Patient, c.Provider, row_number() over (partition by c.Patient, c.Provider order by count(*) desc) as seq  
      from cte c
      group by Patient, Provider
     ) c
where seq = 1;

I can't go with your current attempt. So, i just assumed with simple cte (containing some JOINs).

edited Oct 15 '18 at 15:44

S3S

24,809
5
26
45

answered Oct 15 '18 at 15:22

Yogesh Sharma

49,870
5
26
52

You were very helpful, however I can't mark this as the correct answer because your syntax is off.. Row_number() OVER(Partition by .... does work. I also didn't understand the CTE stuff. But what you initially answered with before you added the CTE code made sense. Thank you! – Irena Rich Oct 15 '18 at 15:43
1

@scsimon. . . Thanks. – Yogesh Sharma Oct 15 '18 at 15:45
@IrenaSagan. . . Yes it was typo corrected by scsimon. – Yogesh Sharma Oct 15 '18 at 15:45

Select Top 1 By Number of Records

2 Answers2