Re: Ask About SQL

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.com