PostgreSQL, how to query the database to retrieve the percentile elements of a dataset?

Question

I need a PostgreSQL query that do the following job: given a percentage (e.g. 10%), the query should return each 1st element of each percentile, among all the elements selected.

So suppose in this simple case my table contains elements which are integer values between 1 and 100:

id    | value
--    | -----
id1   | 1
id2   | 2
...
id100 | 100

And suppose the input percentile is 10%. The query should divide all the dataset into spans whose range is 10%, and return each first element. So the output should be:

id1 | 1
id11 | 11
id21 | 21
id31 | 31
id41 | 41
id51 | 51
id61 | 61
id71 | 71
id81 | 81
id91 | 91

Does anyone knows how I could write this Sql query? Thanks!

Answer 1

You can utilize NTILE to create n quantiles:

select id, min(value)
from
 (
   select id, value, 
      ntile(10) over (order by value) as nt
   from tab
 ) as dt
group by id, nt