Restricting queries by an analytic function

From: Okey Laboratory <itatsumakiNO_at_SPAMhotmail.com>
Date: Wed, 22 Jan 2003 23:44:35 GMT
Message-ID: <H953AC.9Du_at_campus-news-reading.utoronto.ca>

Hi all,

Might be a silly question that I should have found the answer for somewhere in the Oracle Docs, but... thus far haven't found it.

I'm using the analytical function PERCENT_RANK() to try to trim a dataset. That is, I would like to be able to select the top/bottom 5% of a distribution.

My query looks like this:

SELECT BARCODE,

               S635_MEDIAN,
               1- PERCENT_RANK() OVER (
                              PARTITION BY BARCODE
                              ORDER BY S635_MEDIAN ASC) AS PR

FROM FILTERED_DATA; And the table FILTERED_DATA has barcode as the PK and several thousand (non-distinct) S635_MEDIAN values for each barcode.

I know I can get the trimmed dataset by: a) inserting this into a temp-table and doing a SELECT off of that table b) using PL/SQL and just taking rows from the cursor that match the criteria

However, it seems to me that there should be some way of doing this directly in the SQL, which would be more elegant and much more maintainable. Does anyone have any suggestions?

None of the obvious approaches work, i.e.:

WHERE PERCENT_RANK OVER (

                   PARTITION BY BARCODE
                   ORDER BY S635_MEDIAN ASC) BETWEEN 0.05 and 0.95

WHERE PR BETWEEN 0.05 and 0.95

Nor do those work in a HAVING clause.

Any ideas (or suggestions of other places to look) very much appreciated, Paul Received on Thu Jan 23 2003 - 00:44:35 CET