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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
