CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Thomas
Thomas

Reputation: 366

oracle get the char of the longest charsequence from a "characterlist" in select

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.

Upvotes: 0

Views: 43

Answers (1)

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 2

Related Questions