Re: Top N without inline
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 23 Jun 2010 10:03:41 -0700
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
Other related posts:
