Re: Selecting top N percent of records. - Mailing list pgsql-general

Tim Uckun wrote:
> 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