Top N without inline

Hello List,
A friend (report developer) has asked for help, and I have not been able to 
give him a satisfactory answer.  I'm hoping someone on Oracle-L can help out.  
Basically, he wants to run a Top N query that does not use an inline (nested 
SQL statement).  It's easier in Hyperion 11.3 to select the columns and where 
clause, and allow the tool to populate the FROM clause.  It isn't an insoluble 
problem--we could either build a view or he can paste a SQL statement into 
Hyperion, but then any changes to the report spec or table design require that 
additional step.

He's using a dense rank function.  We can generate Top N queries of the form:

SELECT * FROM
       (SELECT pal_attained_mtd, DENSE_RANK () OVER (ORDER BY pal_attained_mtd 
DESC) AS RANKINGS, bdm_name
       FROM bdm_stats_ts
       GROUP BY bdm_name, pal_attained_mtd)
WHERE RANKINGS < 6;

But it would be helpful if we could generate the SQL so that the limitation to 
the Top 5 was done in a unnested SQL statement.

Here's the table structure.  This is a simplified.  The production table has 
columns for different kinds of targets and actual results:
BDM_STATS_TS
Name                                                                    Null?   
 Type
 ----------------------------------------------------------------------- 
-------- -------------------------------------------------
 PAL_ATTAINED_MTD                                                        NOT 
NULL NUMBER(6,2)
 BDM_NAME                                                                NOT 
NULL VARCHAR2(150)

The database is Oracle 10.2.0 running on Solaris 8.  

Thanks in advance,
Dave

Other related posts: