From: Joshua Tolley <eggyknap@gmail.com>
To: Otniel Michael <otnieltera@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Ask About SQL
Date: Wed, 19 Aug 2009 08:00:40 -0600
Message-ID: <20090819140040.GD31216@eddie> (raw)
In-Reply-To: <1703cd180908190317p3b1c4140hc9cbe41331c0725e@mail.gmail.com>
References: <1703cd180908190317p3b1c4140hc9cbe41331c0725e@mail.gmail.com>
On Wed, Aug 19, 2009 at 05:17:07PM +0700, Otniel Michael wrote:
> 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?
> Thanks anyway.
>
> --
> -------------------------------------------------------------------
> "He who is quick to become angry will commit folly, and a crafty man is
> hated"
Your example doesn't match your description (the combination of x1 and y2
isn't listed). However, from your description it looks like what you want is
DISTINCT ON
http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-DISTINCT
Something like this:
SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM A ORDER BY
field1, field2, field3;
Here's an example.
# select * from b order by f1, f2, f3;
f1 | f2 | f3
----+----+----
x1 | y1 | 5
x1 | y2 | 1
x1 | y2 | 3
x2 | y3 | 2
x2 | y3 | 4
(5 rows)
# select distinct on (f1, f2) f1, f2, f3 from b order by 1, 2,
3 desc;
f1 | f2 | f3
----+----+----
x1 | y1 | 5
x1 | y2 | 3
x2 | y3 | 4
(3 rows)
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.comAttachments:
signature.asc (application/pgp-signature, 196B) download
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-sql@postgresql.org
Cc: eggyknap@gmail.com, otnieltera@gmail.com
Subject: Re: Ask About SQL
In-Reply-To: <20090819140040.GD31216@eddie>
* 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