I think that would be a good question :)
So, I have a characterlist like '111122333334458888888888'
and I want to get only the char of the longest sequence.(it's '8' in that example)
It's a maxsearch of course, but I need to do it in the SELECT statement.
You can try something like this:
select character from ( select character, count(1) from ( select substr('111122333334458888888888', level, 1) as character from dual connect by level <= length('111122333334458888888888') ) group by character order by 2 desc ) where rownum = 1
This uses the inner query to split the starting string into single characters, then counts the occurrence of every character ordering to get the character with the greatest number of occurrences. You can rewrite this in different ways, with analytic functions; I believe this way is a one of the most readable.
If you have more than one character with the maximum number of occurrences, this will return one of them, in unpredictable way; if you need to chose, for example, the mimimum char, you can edit the
ORDER BY clause accordingly.