how to find the maximum occurence of a string in Oracle SQL developer

Question

i have 2 columns in a table. Data looks like this

Folio_no | Flag
1145       R

201        S

1145       FR

300        E

1145       R

201        E

201        S

Expected Output:

Folio_No | Flag

1145        R

201         S

300         E

The output should give the folio_no along with the flag which occured maximum number of times for that particular folio number.

i tried doing the below but it throws an error

select folio_no, max(count(flag)) from table group by folio_no;

Answer 1

If you want the flag(s) that have the maximum occurrence for each folio then you can use:

SELECT Folio_No, Flag
FROM   (
  SELECT Folio_No,
         Flag,
         RANK() OVER (PARTITION BY Folio_No ORDER BY COUNT(*) DESC) AS rnk
  FROM   table_name
  GROUP BY Folio_No, Flag
)
WHERE  rnk = 1;

Which, for the sample data:

CREATE TABLE table_name (folio_no, flag) AS
  SELECT 1145, 'R'  FROM DUAL UNION ALL
  SELECT 201,  'S'  FROM DUAL UNION ALL
  SELECT 1145, 'FR' FROM DUAL UNION ALL
  SELECT 300,  'E'  FROM DUAL UNION ALL
  SELECT 1145, 'R'  FROM DUAL UNION ALL
  SELECT 201,  'E'  FROM DUAL UNION ALL
  SELECT 201,  'S'  FROM DUAL UNION ALL
  SELECT 201,  'S'  FROM DUAL UNION ALL
  SELECT   1,  'A'  FROM DUAL UNION ALL
  SELECT   1,  'A'  FROM DUAL UNION ALL
  SELECT   1,  'B'  FROM DUAL UNION ALL
  SELECT   1,  'B'  FROM DUAL UNION ALL
  SELECT   1,  'C'  FROM DUAL UNION ALL
  SELECT   1,  'D'  FROM DUAL;

Outputs:

FOLIO_NO	FLAG
1	A
1	B
201	S
300	E
1145	R

---

If you want only a single flag with the maximum occurrence for each folio, and if there are ties then the first folio alphabetically in each folio, then:

SELECT Folio_No, Flag
FROM   (
  SELECT Folio_No,
         Flag,
         ROW_NUMBER() OVER (PARTITION BY Folio_No ORDER BY COUNT(*) DESC, flag) AS rn
  FROM   table_name
  GROUP BY Folio_No, Flag
)
WHERE  rn = 1;

Which, for the sample data outputs:

FOLIO_NO	FLAG
1	A
201	S
300	E
1145	R

db<>fiddle here