pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Re: Sorted group by
Date: Tue, 10 Aug 2010 18:22:27 +0200
Message-ID: <i3rubv$boj$1@dough.gmane.org> (raw)
In-Reply-To: <alpine.DEB.2.00.1008101702100.2654@aragorn.flymine.org>
References: <alpine.DEB.2.00.1008101627440.2654@aragorn.flymine.org>
	<i3rsrr$23e$1@dough.gmane.org>
	<alpine.DEB.2.00.1008101702100.2654@aragorn.flymine.org>

Matthew Wakeling wrote on 10.08.2010 18:03:
> On Tue, 10 Aug 2010, Thomas Kellerer wrote:
>> No. It's built in (8.4) and it's called Windowing functions:
>> http://www.postgresql.org/docs/8.4/static/tutorial-window.html
>> http://www.postgresql.org/docs/8.4/static/functions-window.html
>>
>> SELECT group, last_value(value) over(ORDER BY number)
>> FROM table
>
> I may be mistaken, but as I understand it, a windowing function doesn't
> reduce the number of rows in the results?

Yes you are right, a bit too quick on my side ;)

But this might be what you are after then:

select group_, value_
from (
   select group_, value_, number_, row_number() over (partition by group_ order by value_ desc) as row_num
   from numbers
) t
where row_num = 1
order by group_ desc

view thread (11+ messages)  latest in thread

Message-ID: <i3rubv$boj$1@dough.gmane.org>
Permalink:  ../i3rubv$boj$1@dough.gmane.org/
Also on:    postgresql.org/message-id/i3rubv$boj$1@dough.gmane.org

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: spam_eater@gmx.net
  Subject: Re: Sorted group by
  In-Reply-To: <i3rubv$boj$1@dough.gmane.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox