Re: Top N without inline
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: davepacia@xxxxxxxxx
- Date: Wed, 23 Jun 2010 18:05:50 +0200
This might do the trick:
WITH myview as
(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)
SELECT pal_attained_mtd, RANKINGS , bdm_name from myview
WHERE RANKINGS < 6;
Not sure if this fits your requirement, but it doesn't use an "inline-view"
as per its definition :)
Stefan
=========================
Stefan P Knecht
CEO & Founder
s@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Wed, Jun 23, 2010 at 4:57 PM, 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
>
>
Other related posts:
