1

The code below gives me something similar to the table below. What I am looking to do is only return the PROVID that has the max count per PATID.

SELECT  PAT_ID AS PATID
    , VISIT_PROV_ID AS PROVID
    , COUNT(*) AS PROVCOUNT
    FROM PAT_ENC
    GROUP BY PAT_ID, VISIT_PROV_ID
    ORDER BY PAT_ID, COUNT(*) DESC
PATID PROVID PROVCOUNT
1 3 1
2 4 6
2 3 2
2 8 1
3 4 6
4 1 8
4 2 3

The table below would be the desired result based on the same data from the previous table.

PATID PROVID PROVCOUNT
1 3 1
2 4 6
3 4 6
4 1 8
MT0
  • 143,790
  • 11
  • 59
  • 117
Brennan Cushin
  • 11
  • 1

1 Answers1

1

We can user RANK() OVER (PARTITION BY PATID ORDER BY PROVCOUNT DESC), which gives a ranking of 1 to the row with largest value of PROVCOUNT for each PATID, in a CTE and then use WHERE ranking = 1 to only show these largest values

create table PAT_ENC (
PATID int,
PROVID    int,
PROVCOUNT int);

INSERT INTO PAT_ENC (PATID, PROVID, PROVCOUNT) 
SELECT 1, 3,  1 FROM DUAL UNION ALL
SELECT 2, 4,  6 FROM DUAL UNION ALL
SELECT 2, 3,  2 FROM DUAL UNION ALL
SELECT 2, 8,  1 FROM DUAL UNION ALL
SELECT 3, 4,  6 FROM DUAL UNION ALL
SELECT 4, 1,  8 FROM DUAL UNION ALL
SELECT 4, 2,  3 FROM DUAL;

WITH COUNTED AS
(SELECT 
  PATID, 
  PROVID, 
  PROVCOUNT,
  RANK() OVER (PARTITION BY PATID ORDER BY PROVCOUNT DESC) ranking
FROM PAT_ENC)
SELECT
  PATID, 
  PROVID, 
  PROVCOUNT
FROM COUNTED
WHERE ranking = 1;

PATID | PROVID | PROVCOUNT
----: | -----: | --------:
    1 |      3 |         1
    2 |      4 |         6
    3 |      4 |         6
    4 |      1 |         8

db<>fiddle here