Only return rows with local max [duplicate]

Question

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

Answer 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