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

CodePudding user response：

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"