Re: Re: [GENERAL] question (or feature-request): over ( partition by ... order by LIMIT N)

2010/3/26 David Fetter <david(at)fetter(dot)org>:
> On Wed, Mar 24, 2010 at 06:31:59PM +0100, A. Kretschmer wrote:
>> Hello @all,
>>
>> I know, i can do:
>>
>> select * from (select ... row_number() over (...) ...) foo where
>> row_number < N
>>
>> to limit the rows per group, but the inner select has to retrieve
>> the whole set of records and in the outer select most of them
>> discarded.
>
> That sounds like the optimizer's falling down on the job.  Would this
> be difficult to fix?

I believe this isn't the task of window functions. In fact, "over( ...
LIMIT n)" or optimizer hack will fail on multiple window definitions.

To take top N items of each group (I agree this is quite common job),
I'd suggest syntax that is done by extending DISTINCT ON.

SELECT DISTINCT n ON(key1, key2) ...

where "n" means top "n" items on each "key1, key2" group. The current
DISTINCT ON() syntax is equivalent to DISTINCT 1 ON() in this way.
That'll be fairly easy to implement and you aren't be bothered by this
like multiple window definitions. The cons of this is that it can be
applied to only row_number logic. You may want to use rank,
dense_rank, etc. sometimes.

Regards,

--
Hitoshi Harada