On Feb 7, 12:51 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> Your clarification helps. What we need to do is something like this,
> which is similar to the PERCENT_RANK(), but 1 is added to the COUNT of
> the records:
>
> SELECT
> N,
> (ROW_NUMBER() OVER (ORDER BY N))/(COUNT(*) OVER (PARTITION BY 1)+1)
> POSITION_RANKING
... OK I think that is the best way. Works, & fast.
[BTW, using just COUNT(*), instead of COUNT(*)+1, in the
denominatorseems more intuitive to me. E.g. for ten rows
the "rank" would rise in increments of 0.1 and not 1/9.
In this case you would need to use the comparisons ">"
and "<=" instead of BETWEEN. However I cannot find any cases
for which the two methods produce differente results, at
least for rowsets of size N or larger when you are "trimming"
1/N of rows on either end.]
Received on Wed Feb 07 2007 - 13:32:38 CST
