Re: Restricting queries by an analytic function

This should do it:

select * from (your select statement) where pr between ......;

or do you have something else in mind?

• Jusung Yang

"Okey Laboratory" <itatsumakiNO_at_SPAMhotmail.com> wrote in message news:<H953wK.9Ks_at_campus-news-reading.utoronto.ca>...
> 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 - 17:57:21 CET