From: Jonathan Blitz <jblitz@013.net>
To: pgsql-performance@postgresql.org
Subject: Re: Sorted group by
Date: Tue, 10 Aug 2010 20:38:02 +0300
Message-ID: <80C83A54DDD9410F87FC9DDC78967ED0@jblaptop> (raw)
In-Reply-To: <4C613A04020000250003441E@gw.wicourts.gov>
References: <alpine.DEB.2.00.1008101627440.2654@aragorn.flymine.org>
<4C613A04020000250003441E@gw.wicourts.gov>
Another couple of possible ways:
Select groupfield,value
From tbl x1
Where number = (select max(number) from tbl x2 where x2.groupfield=
x1.groupfield)
Select groupfield,value
From tbl x1
Where (groupfield,number) in (select groupfield,max(number) from tbl group
by groupfield)
Which is quickest?
Probably best to try out and see.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: Tuesday, August 10, 2010 7:38 PM
To: Matthew Wakeling; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Sorted group by
Matthew Wakeling <matthew@flymine.org> wrote:
> I'm trying to eke a little bit more performance out of an application
In addition to the suggestion from Thomas Kellerer, it would be interesting
to try the following and see how performance compares using real data.
select group, value from tbl x
where not exists
(select * from tbl y
where y.group = x.group and y.number > x.number);
We have a lot of code using this general technique, and I'm curious whether
there are big gains to be had by moving to the windowing functions. (I
suspect there are.)
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.851 / Virus Database: 271.1.1/3061 - Release Date: 08/09/10
21:35:00
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: jblitz@013.net
Subject: Re: Sorted group by
In-Reply-To: <80C83A54DDD9410F87FC9DDC78967ED0@jblaptop>
* 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