Re: Top N without inline
- From: Dave Pacia <davepacia@xxxxxxxxx>
- To: Michael Dinh <mdinh@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2010 08:57:48 -0700 (PDT)
Thanks Michael. Tom Kyte gives a good discussion of using inlines to generate
Top N or getting rows between N and M. But all such queries use inlines.
We're trying to avoid using an inline.
Dave
________________________________
From: Michael Dinh <mdinh@xxxxxxxxx>
To: "davepacia@xxxxxxxxx" <davepacia@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Sent: Wed, June 23, 2010 11:24:17 AM
Subject: RE: Top N without inline
Here is a good link from Tom Kytes.
http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html
Michael Dinh : XIFIN : 858.436.2929
NOTICE OF CONFIDENTIALITY - This material is intended for the use
of the individual or entity to which it is addressed, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED
HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE
FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY
ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT
NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE.
If the reader of this email (and attachments) is not the intended recipient,
you are hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. Please notify the sender of the error and
delete the e-mail you received. Thank you.
From:oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Dave Pacia
Sent: Wednesday, June 23, 2010 7:58 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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:
