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

database, postgresql, postgresql-9.1, sql

Solution

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.

Problem

We are new to postgres, we have following query by which we can select top N records from each category. ``` create table temp ( gp char, val int ); insert into temp values ('A',10); insert into temp values ('A',8); insert into temp values ('A',6); insert into temp values ('A',4); insert into temp values ('B',3); insert into temp values ('B',2); insert into temp values ('B',1); select a.gp,a.val from temp a where a.val in ( select b.val from temp b where a.gp=b.gp order by b.val desc limit 2); ``` Output of above query is something like this ``` gp val ---------- A 10 A 8 B 3 B 2 ``` But our requirement is different, we want to select top n% records from each category where n is not fixed, n is based of some percent of elements in each group.

Original source