Re: Selecting top N percent of records.

On 10/18/2010 08:06 AM, Tim Uckun wrote:
>> That is a bit problematic because it necessitates knowing the number
>> of rows total, and slow counting is an idiosyncrasy of postgres.
>>
>> http://wiki.postgresql.org/wiki/Slow_Counting
>>
>> To get the top 10%:
>>
>> SELECT * FROM table LIMIT(SELECT (COUNT(*) * 0.1)::integer FROM table)
>
>
> I think I wasn't making myself clear. I don't want the top 10% of the
> rows. I want the rows with the top 10% of the values in a column.

OK, so you want a median-style "sort them in descending order and count
down until you've selected the first 10% of rows" approach? In other
words, values in the 90th percentile of the distribution?

Try this. Given table "x" with single integer column "y", obtain rows of
x in the 90th percentile of y:

select ranked.y FROM (select percent_rank() over (order by y desc) as
pc, y from x) AS ranked WHERE pc <= 0.1;

or:

select ranked.y from (select ntile(10) over (order by y desc) as pc, y
from x) AS ranked WHERE pc = 1;

See:

http://www.postgresql.org/docs/current/static/functions-window.html

Both of these seem to produce odd results with small input row counts.
Test carefully before trusting these expressions, as I'm quite new to
the use of window functions.

--
Craig Ringer