Re: Restricting queries by an analytic function

That works like a charm. Thanks very kindly!

Paul

"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message news:130ba93a.0301230857.53d6dba1_at_posting.google.com...
> 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 - 23:50:34 CET