Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E248363296C for ; Wed, 19 Aug 2009 07:47:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 19338-04 for ; Wed, 19 Aug 2009 10:46:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.187]) by mail.postgresql.org (Postfix) with ESMTP id E5EE263356E for ; Wed, 19 Aug 2009 07:46:53 -0300 (ADT) Received: from webserv.wug.de ([194.25.154.138]) by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis) id 0MKv5w-1Mdigr0WOD-000441; Wed, 19 Aug 2009 12:46:50 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1Mdigq-0004Q8-00 for pgsql-sql@postgresql.org; Wed, 19 Aug 2009 12:46:48 +0200 Date: Wed, 19 Aug 2009 12:46:48 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Ask About SQL Message-ID: <20090819104648.GA11859@a-kretschmer.de> References: <1703cd180908190317p3b1c4140hc9cbe41331c0725e@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <1703cd180908190317p3b1c4140hc9cbe41331c0725e@mail.gmail.com> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! User-Agent: Mutt/1.5.9i X-Provags-ID: V01U2FsdGVkX1+jqfE8/NAz4MlaVnR8gQMAh6JSZSweZdg7DsY YiZUBnxQb0oiuSsOPk9LqBOpd7qcK0Tyyo7e7g+uB2OLk53hPh QK+swa6pwsCK2SWAkFsRg== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/102 X-Sequence-Number: 33011 In response to Otniel Michael : > Hi All. > > Can help to create sql queries for this data : > > tabel A > field1, field2, field3 > x1,     y1,     5 > x1,     y2,     1 > x2,     y1,     2 > x2,     y3,     4 > x1,     y3,     4 > > I want to get 2 record with the max value at field3 for each kombination of > field1 : > > tabel B > field1, field2, field3 > x1,     y1,     5 > x1,     y3,     4 > x2,     y3,     4 > x2,     y1,     2 > > Anyone have an ideas? Works since 8.4: test=*# select * from table_a ; field1 | field2 | field3 --------+--------+-------- x1 | y1 | 5 x1 | y2 | 1 x2 | y1 | 2 x2 | y3 | 4 x1 | y3 | 4 (5 rows) test=*# select field1, field2, field3 from (select field1, field2, field3, row_number() over(partition by field1 order by field3 desc) from table_a order by field1, field3) foo where row_number < 3 order by field1, field2; field1 | field2 | field3 --------+--------+-------- x1 | y1 | 5 x1 | y3 | 4 x2 | y1 | 2 x2 | y3 | 4 (4 rows) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net