Selecting top N percent of records.

[Tim Uckun]

Is there a way to select the top 10% of the values from a column?

For example the top 10% best selling items where number of sales is a column.

Thanks.

[Darren Duncan]

Post by Tim Uckun
Is there a way to select the top 10% of the values from a column?
For example the top 10% best selling items where number of sales is a column.

The top 10% would be a variable number of records. Is that what you want, or
would you rather, say, just see the top N items?

The latter is easy, just something like this:

SELECT * FROM mytbl ORDER BY num_sales DESC LIMIT $1

... where $1 is the number of records you want.

To actually get 10% of the records, you can replace the $1 with a subquery,
something like this probably:

SELECT * FROM mytbl ORDER BY num_sales DESC
LIMIT (SELECT (count(*) / 10) AS selnum FROM mytbl)

There are probably other ways to do it also.

Maybe you want all items whose sales are in the top 90 percentile or something,
or maybe you want what generated the most profit, etc.

-- Darren Duncan

[Tim Uckun]

Post by Tim Uckun
Is there a way to select the top 10% of the values from a column?
For example the top 10% best selling items where number of sales is a column.

The top 10% would be a variable number of records.  Is that what you want,
or would you rather, say, just see the top N items?

Yes this is what I want. I don't care how many records I pull up (well
I might want to put a LIMIT on them). I want the top 10%

[Peter Geoghegan]

Post by Tim Uckun
Is there a way to select the top 10% of the values from a column?
For example the top 10% best selling items where number of sales is a column.

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)

[Tim Uckun]

Post by Peter Geoghegan
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
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.

In my case there is a very non linear set of values. The lowest value
is 1 and the vast majority of records have a 1 in the column. The
highest value might be in the tens of thousands. I want to pull up
the records that have the top ten percent values.

[Craig Ringer]

Post by Tim Uckun

Post by Peter Geoghegan
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
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

[Tim Uckun]

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
select ranked.y FROM (select percent_rank() over (order by y desc) as pc, y
from x) AS ranked WHERE pc <= 0.1;
select ranked.y from (select ntile(10) over (order by y desc) as pc, y from
x) AS ranked WHERE pc = 1;

Thanks I will look into the window functions. I haven't used them
before so thanks for the tip.

[Brent Wood]

Something like this should work - (but is untested), and does require the extra subquery, so there may be a more efficient way?

However, off the top of my head:

select a,b,c
from table
where
order by c desc
limit (select count(*)/10 from table where );


where c is the no of sales column



Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand

Tim Uckun 10/18/10 3:40 PM >>>

Is there a way to select the top 10% of the values from a column?

For example the top 10% best selling items where number of sales is a column.

Thanks.