I have two tables, table1 and table2. Let the two tables contain date, id and latency column.

I have a simple query that performs a join on the two tables and returns a set of rows:

Select table1.date,(table2.latency - table1.latency) as ans from table1, table2
where table1.id = table2.id order by ans;

I need to find the nth percentile row from the returned set of rows, lets say I need to find 90%, 99% and 99.9% percentile row from the data.

I need to display the data in a form like this:

    date       |   percentile  | ans
    01-12-1995 |    90         | 0.001563
    02-12-1999 |    99         | 0.0015
    05-12-2000 |    99.9       | 0.012

This is my first encounter to PostgreSQL. I am confused as to how should I proceed.

I was having a look at PERCENT_RANK() function. Please guide me in the correct direction.

  • Please provide sample data and explain the order in which the percentile should be calculated. Commented Dec 24, 2013 at 12:21
  • tip use SQLfiddle to provide us with example data.. you should really elaborate your question more.. and it looks like you want to use date as an GROUP where columns percentile and ans (looks like an SUM) depends on Commented Dec 24, 2013 at 12:34
  • well i need the exact/closest row indicating that it is the 90 percentile value for the given set of data.so when i get the row, i want to display only that particular row.
    – D3XT3R
    Commented Dec 24, 2013 at 12:39
  • You need to declare the version of Postgres you are working with. And, of course, an sqlfiddle or any kind of sample data would be welcome. Commented Dec 24, 2013 at 14:19

1 Answer 1


Use the window function ntile() in a subquery (requires Postgres 8.4 or later).
Then select the segments you are interested in (corresponding to percentiles) and pick the row with the lowest value from it:

       the_date, to_char((segment - 1)/ 10.0, '99.9') AS percentile, ans
    SELECT t1.the_date 
          ,ntile(1000) OVER (ORDER BY (t2.latency - t1.latency)) AS segment
          ,(t2.latency - t1.latency) AS ans
    FROM   table1 t1
    JOIN   table2 t2 ON t1.id = t2.id
   ) sub
WHERE  segment IN (601, 901, 991, 1000)
ORDER  BY segment, ans;

The Postgres-specific DISTINCT ON comes in handy for the last step. Detailed explanation in this related answer on SO:
Select first row in each GROUP BY group?

To get the 90, 99 and 99.9 percentile I picked the matching granularity with ntile(1000). And added a 60 percentile as per comment.

This algorithm picks the row at or above the exact value. You can add a line to the subquery with percent_rank() to get the exact relative rank of the select row in addition:

 percent_rank() OVER (ORDER BY (t2.latency - t1.latency)) AS pct_rank

Aside: I replaced the column name date with the_date since I am in the habbit of avoiding reserved SQL key words as identifiers, even if Postgres would permit them.

  • @D3XT3R: Actually my selection of segments was off. I fixed that and added the row above 60%. Commented Dec 24, 2013 at 14:52
  • so how do you base your selection of segments..i mean lets say.. i want to display percentile value of 100 percentile within the same algorith. as per your algorithm, i need to provide the segments of data i want to create, am i on the correct track?
    – D3XT3R
    Commented Dec 24, 2013 at 14:56
  • Since I divided the rows into 1000 segments, the lowest value of the 601st (or the highest value of the 600th) would mark the 60% border ... Commented Dec 24, 2013 at 15:00
  • thanks erwin..how can i get the last row of the table i.e. the 100 percentile value? should i divide the rows into further more segments?
    – D3XT3R
    Commented Dec 24, 2013 at 15:04
  • @D3XT3R: Use a descending order and add LIMIT 1 to get the last row in a simple query.. SELECT * FROM .. ORDER BY ans DESC LIMIT 1. Or, in the above query, to pick the row with the highest value from the segment, add DESC at the very end. You would pick the segments (600, 900, 990, 1000) in this case and adapt the to_char() expression accordingly. Commented Dec 24, 2013 at 15:21

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.