Re: Selecting top N percent of records.

From: Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>
To: Tim Uckun <timuckun(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Selecting top N percent of records.
Date: 2010-10-17 23:56:02
Message-ID: AANLkTi=i9G+5aVqvfnbB2RKGL4Rtfnh_PRN_aZOCu2te@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 18 October 2010 00:33, Tim Uckun <timuckun(at)gmail(dot)com> 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.
>

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)

--
Regards,
Peter Geoghegan

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tim Uckun 2010-10-18 00:03:29 Re: Selecting top N percent of records.
Previous Message Kynn Jones 2010-10-17 23:54:25 Re: Fastest way to check database's existence