Skip site navigation (1) Skip section navigation (2)

Re: Ask About SQL

From: Joshua Tolley <eggyknap(at)gmail(dot)com>
To: Otniel Michael <otnieltera(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Ask About SQL
Date: 2009-08-19 14:00:40
Message-ID: 20090819140040.GD31216@eddie (view raw or flat)
Thread:
Lists: pgsql-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

In response to

pgsql-sql by date

Next:From: Yeb HavingaDate: 2009-08-19 15:15:24
Subject: Re: Multiple simultaneous queries on single connection
Previous:From: A. KretschmerDate: 2009-08-19 10:46:48
Subject: Re: Ask About SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group