Postgresql : How do I select top n percent(%) entries from each group/category
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, valfrom ( select gp, val, count(*) over (partition by gp) as cnt, row_number() over (partition by gp order by val desc) as rn from temp) twhere 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.
Referencing the response from a_horse_with_no_name, you can achieve something similar using percent_rank()
SELECT gp, val, pct_rankFROM ( SELECT gp, val, percent_rank() over (order by val desc) as pct_rank FROM variables.temp ) tWHERE pct_rank <= 0.75;
You can then set the final WHERE clause to return data at whatever percent_rank() threshold you require.
The accepted answer did not work for me. I find this solution that works for me:
SELECT * FROM temp ORDER BY val DESC LIMIT (SELECT (count(*) / 10) AS selnum FROM temp )
It is not optimal (performance) but it works