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

Re: sql interogation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "cristivoinicaru" <cristivoinicaru(at)dmhi(dot)ct(dot)ro>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: sql interogation
Date: 2004-10-25 14:06:13
Message-ID: 18153.1098713173@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"cristivoinicaru" <cristivoinicaru(at)dmhi(dot)ct(dot)ro> writes:
> I want like sql interogation to select only one record from each "gc" group
> record (who has the same code "gc" (that means that the "gc" field will be
> unique key)) with the following two condition:
> 1)from each "gc" group of records to select the record who has the value of
> "ora" field maxim.
> 2)if two ore more record have the same value of the maxim value of the "ora"
> to select oly one of them

I don't think you can do (2) very nicely in standard SQL, because the
results aren't fully defined and the SQL standard does not like poorly
defined behaviors ;-).  However there is a nonstandard extension in
Postgres that is exactly what you need: SELECT DISTINCT ON.  See the
"weather reports" example in the SELECT reference page --- your "gc"
corresponds to weather stations and "ora" to report date.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Ron St-PierreDate: 2004-10-25 16:17:34
Subject: Re: [pgfsupport - Help] RE: A big red x
Previous:From: Steven KlassenDate: 2004-10-25 14:00:40
Subject: Re: sql interogation

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