Thomas Thomas - 2 years ago 80
SQL Question

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.

Answer Source

You can try something like this:

select character
    select character, count(1)
        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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download