Return maximum occurring character in an input string though Oracle SQL

Question

Say if I have fruit names like Papaya,Orange,etc row by row...

How will I find the maximum occuring character in each string. For Papaya it will be 'a' as it was repeated 3 times For Orange it will be all the characters as for each character it was repeated one time only I need to solve the above query using Oracle SQL

Answer 1

Here's one option:

SQL> WITH
  2     fruit (name)
  3     AS
  4        (SELECT 'Papaya' FROM DUAL
  5         UNION ALL
  6         SELECT 'Orange' FROM DUAL),

---

  7     temp
  8     AS
  9        (SELECT name, SUBSTR (name, COLUMN_VALUE, 1) letter
 10           FROM fruit
 11                CROSS JOIN
 12                TABLE (
 13                   CAST (
 14                      MULTISET (    SELECT LEVEL
 15                                      FROM DUAL
 16                                CONNECT BY LEVEL <= LENGTH (name))
 17                         AS SYS.odcinumberlist))),
 18     temp2
 19     AS
 20        (  SELECT name,
 21                  letter,
 22                  COUNT (*) cnt,
 23                  RANK () OVER (PARTITION BY name ORDER BY COUNT (*) DESC) rnk
 24             FROM temp
 25         GROUP BY name, letter)
 26    SELECT name,
 27           LISTAGG (letter, ', ') WITHIN GROUP (ORDER BY letter) letters,
 28           cnt
 29      FROM temp2
 30     WHERE rnk = 1
 31  GROUP BY name, cnt;

NAME   LETTERS                     CNT
------ -------------------- ----------
Orange O, a, e, g, n, r              1
Papaya a                             3

SQL>

• TEMP CTE splits names into rows (by each letter)
• TEMP2 ranks them by count in descending order
• final select returns letters that rank the "highest"

Answer 2

I have another idea.

When data is as following.

create table t as 
  select 'Papaya' w from dual union all
  select 'Orange' w from dual union all
  select 'Baby' w from dual union all
  select 'CocaCola' w from dual

Get maximum occurrence of alphabet and count as following

with cte as (
  select w, az.c find, regexp_count(w, az.c, 1, 'i') cnt
  from   t,
         (select chr(rownum + 96) c
          from   dual
          connect by rownum <= 26
         ) az
),
cte2 as (
  select w, max(lpad(cnt, 10, '0') || find) cnt_find
  from   cte
  group by w
)
select w, substr(cnt_find, 11, 999) find, to_number(substr(cnt_find, 1, 10)) cnt
from   cte2

Result:

| W        | FIND | CNT |
|----------|------|----:|
| Orange   | r    |   1 |
| Papaya   | a    |   3 |
| Baby     | b    |   2 |
| CocaCola | c    |   3 |

Because alphabet set is limited to 26 kind, I generated 26 rows to cross join with word table.

select chr(rownum + 96) c
from   dual
connect by rownum <= 26

And used regexp_count to get occurrence count of each alphabet.

select w, az.c find, regexp_count(w, az.c, 1, 'i') cnt

And retrieved maximum occurrence followed by alphabet groupped by word. (lpad used to fill 10 length for all occurrence to use substr next step)

select w, max(lpad(cnt, 10, '0') || find) cnt_find
from   cte
group by w

And divided maximum of occurrence and alphabet. (substr is 1 to 10, and 11 to end because all maximum occurrence is 10 length)

select w, substr(cnt_find, 11, 999) find, to_number(substr(cnt_find, 1, 10)) cnt
from   cte2

This link shows how I made this query in detail.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=29516e70a1270c4035d7165832905777

---

Show all winner instead of first winner

with cte as (
  select w, az.c find, regexp_count(w, az.c, 1, 'i') cnt
  from   t,
         (select chr(rownum + 96) c
          from   dual
          connect by rownum <= 26
         ) az
), rank_t as 
(
  select w, find, cnt,
         rank() over (partition by w order by cnt desc) rank_cnt
  from   cte
)
select w,
       listagg(find, ', ') within group (order by w) find_list,
       max(cnt) max_cnt
from   rank_t
where  rank_cnt = 1
group by w

Result:

| W        | FIND_LIST        | MAX_CNT |
|----------|------------------|--------:|
| Baby     | b                |       2 |
| CocaCola | c                |       3 |
| Orange   | a, e, g, n, o, r |       1 |
| Papaya   | a                |       3 |

Previous version shows only largest one maximum occurrence. So I used rank function to get all maximum occurrence as number 1

select w, find, cnt,
       rank() over (partition by w order by cnt desc) rank_cnt
from   cte

And filtered only number 1 and converted rows to a column with listagg

select w,
       listagg(find, ', ') within group (order by w) find_list,
       max(cnt) max_cnt
from   rank_t
where  rank_cnt = 1
group by w

This second link shows how I made this query in detail.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c0c15ccb9974d0c4fd14a34408f69fb7