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

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

|improve this answer|||||
  • Any chance you can provide an example? The data column is the table's unique column... – Bryan May 16 '15 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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