There is no way to filter on a window function w/o using an inline view/derived table or a CTE (common table expression) [a.k.a. subquery factoring]. The reason is that the window function is applied *after* the where/group by, etc. On Wed, Jun 23, 2010 at 7:57 AM, Dave Pacia <davepacia@xxxxxxxxx> wrote: > 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 > > -- Regards, Greg Rahn http://structureddata.org