X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B2F983A3B6D for ; Mon, 25 Oct 2004 11:04:32 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23832-02 for ; Mon, 25 Oct 2004 10:04:23 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 46E863A3B24 for ; Mon, 25 Oct 2004 11:04:23 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 8FA9220D87; Mon, 25 Oct 2004 13:07:44 +0300 (EEST) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09736-10; Mon, 25 Oct 2004 13:07:43 +0300 (EEST) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 5164220D85; Mon, 25 Oct 2004 13:07:43 +0300 (EEST) Message-ID: <003001c4ba7a$0fbb3500$0b00a8c0@forge> From: "Andrei Bintintan" To: "cristivoinicaru" , References: <013901c4ba6a$8771d320$7202a8c0@aaa> Subject: Re: sql Date: Mon, 25 Oct 2004 13:04:46 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/167 X-Sequence-Number: 19270 > 2)if two ore more record have the same value of the maxim value of the "ora" > to select oly one of them At this point you really have to decide which criteria you use for this deciding. I strongly suggest to use a uniqe ID for each row in the table(in your case it is important): CREATE TABLE "temp50" ( id serial primary key, "gc" character varying(36), "co" character varying(7), "data" date, "ora" smallint ); Your selection can be made in many ways, but you have to decide the second criteria. One more thing: Your table datas are: 1 >> 5003707G9G9419 22012BB 10-14-2004 82 2 >> 5003707G9G9419 22012BC 10-14-2004 44 3 >> 5003707G9G9419 22022BB 10-14-2004 82 4 >> 5003707G9G9420 22022BC 10-18-2004 49 The rows no 1 and 3 are "redundant" data's(they are the same). Do you really want this??? Best regards, Andy. ----- Original Message ----- From: "cristivoinicaru" To: Sent: Monday, October 25, 2004 11:13 AM Subject: [SQL] sql > 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! > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >