A Perfect String (SQL Puzzle 5)

Problem : A 'perfect' string is a string where the index of the first occurence of each letter is equal to the number of occurences of that letter in the string. For example 'ABB' is perfect since letter 'A' occurs at position 1 and number of 'As' in string is also 1. Letter 'B' occurs at position 2 and total number of Bs is also 2. Similary string 'ABCBCC' is also perfect but string 'ABDDD' is not perfect.
Given a string, write an SQL query to determine whether its perfect or not.

One of the solutions:


select
decode(count (distinct (case when rn=count_individual then 'Perfect' else 'Not Perfect' end)),1,max((case when rn=count_individual then 'Perfect' else 'Not Perfect' end)),'Not Perfect') result from
(
select
rownum rn,
substr(&mystring,rownum,1) individual,
count(substr(&mystring,rownum,1)) over (partition by substr(&mystring,rownum,1)) count_individual ,
row_number() over (partition by substr(&mystring,rownum,1) order by substr(&mystring,rownum,1) ) row_number
from
(
select &mystring from all_objects
)
where
rownum<=length(&mystring)
)
where row_number=1