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

January 24, 2022

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;

>Solution :

We can use an aggregation:

WITH cte AS (
    SELECT Folio_No, Flag, COUNT(*) AS cnt
    FROM yourTable
    GROUP BY Folio_No, Flag
),
cte2 AS (
    SELECT t.*, RANK() OVER (PARTITION BY Folio_No ORDER BY cnt DESC, Flag) rnk
    FROM cte t
)

SELECT Folio_No, Flag
FROM cte2
WHERE rnk = 1;

Note that I assume should two flags within a given folio number be tied for the max frequency, that you want to report the earlier flag.

Here is a working demo.