RE: Top N without inline

From: Michael Dinh <mdinh_at_XIFIN.Com>
Date: Wed, 23 Jun 2010 08:24:17 -0700
Message-ID: <D29F9902E534D5478F2E83FD6A44B306597C8728_at_mail02.mba.xifin.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dave Pacia Sent: Wednesday, June 23, 2010 7:58 AM
To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 23 2010 - 10:24:17 CDT