Thread: sql interogation

I have a postgres table like this:

CREATE TABLE "temp50" (
 "gc" character varying(36),
 "co" character varying(7),
 "data" date,
 "ora" smallint

);

It contains the following records:

5003707G9G9419 22012BB 10-14-2004 82
5003707G9G9419 22012BC 10-14-2004 44
5003707G9G9419 22022BB 10-14-2004 82
5003707G9G9420 22022BC 10-18-2004 49

I'd  like the result of the sql interogation to be like this:

5003707G9G9419 22012BB 10-14-2004 82
5003707G9G9420 22022BC 10-18-2004 49


Explanations:
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

Thanks!

* cristivoinicaru <cristivoinicaru@dmhi.ct.ro> [2004-10-25 10:05:22 +0200]:

> Explanations:

> 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

Try this out; this is from memory, so it hasn't been tested.

SELECT DISTINCT max(gc), co, data, ora FROM temp50 GROUP BY co, data, ora;

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564

"cristivoinicaru" <cristivoinicaru@dmhi.ct.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