Re: Restricting queries by an analytic function

Incidentally, I should clarify one thing: Ultimately I don't want access to the actual rows from this trimmed dataset, I just want to go ahead and calculate the mean and stdev of the distribution returned by it. I'm not sure if that makes a difference, but thought it was worth noting.

Paul

"Okey Laboratory" <itatsumakiNO_at_SPAMhotmail.com> wrote in message news: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:57:56 CET