Postgresql : How do I select top n percent(%) entries from each group/category

Referencing the response from a_horse_with_no_name, you can achieve something similar using percent_rank()

SELECT
    gp,
    val,
    pct_rank
FROM (
    SELECT
        gp,
        val,
        percent_rank() over (order by val desc) as pct_rank
    FROM variables.temp
    ) t
WHERE pct_rank <= 0.75;

You can then set the final WHERE clause to return data at whatever percent_rank() threshold you require.


To retrieve the rows based on the percentage of the number of rows in each group you can use two window functions: one to count the rows and one to give them a unique number.

select gp,
       val
from (
  select gp, 
         val,
         count(*) over (partition by gp) as cnt,
         row_number() over (partition by gp order by val desc) as rn
  from temp
) t
where rn / cnt <= 0.75;

SQLFiddle example: http://sqlfiddle.com/#!15/94fdd/1


Btw: using char is almost always a bad idea because it is a fixed-length data type that is padded to the defined length. I hope you only did that for setting up the example and don't use it in your real table.