2

Say I have a table like the one described below.

                                     Table "public.foo"
  Column   |            Type             |                    Modifiers                 
-----------+-----------------------------+-------------------------------------------------
 id        | integer                     | not null default nextval('foo_id_seq'::regclass)
 data      | text                        | not null
 score     | integer                     |

To calculate the percentile rank for a particular score, I can do something like this:

test=# select percent_rank(5) within group (order by score) as rank from foo;
        rank     
--------------------
 0.0762917366163029
(1 row)

What I'd really like to do is have a select statement that shows the data, score, and rank for each row present. I tried the below query.

test=# select data, score, percent_rank(score) within group (order by score) as rank from foo group by data, score;

This produces a rank of 0 for every row. I'm assuming this is because the within group clause ends up being limited by group by clause, but I'm not enough of a SQL ninja to know for sure.

At any rate, is it possible to include the percentile rank of a row's score as part of a multi-row select?

1 Answer 1

-1

If you include ID I would assume that you can join it back to itself on ID to get that information in one dataset.

1
  • Any chance you can provide an example? The data column is the table's unique column...
    – Bryan
    May 16, 2015 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.