Thread: 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!
> 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" <cristivoinicaru@dmhi.ct.ro> To: <pgsql-sql@postgresql.org> 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 >
On Mon, 25 Oct 2004 10:13:37 +0200, cristivoinicaru <cristivoinicaru@dmhi.ct.ro> wrote: > 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 Try: SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC; --miker > > Thanks! > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
On Monday 25 October 2004 05:20, Mike Rylander wrote: > SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC; You can not have have expressions (columns etc.) in the SELECT list that are either not in a GROUP BY clause, or used in an aggregate function when you use GROUP BY in the statement. By saying SELECT *, means you would have to GROUP BY gc, co, data, ora ... That isn't going to do what he wants. And the SQL you stated should give you an error: ERROR: column "temp50.co" must appear in the GROUP BY clause or be used in an aggregate function What he wants to do is use DISTINCT ON: SELECT DISTINCT ON (gc) gc, co, data, ora FROM temp50 ORDER BY gc, ora DESC; Andy
On Mon, 25 Oct 2004 05:44:06 -0600, Andrew J. Kopciuch <akopciuch@bddf.ca> wrote: > On Monday 25 October 2004 05:20, Mike Rylander wrote: > > SELECT * FROM temp50 GROUP BY gc ORDER BY ora DESC; > > You can not have have expressions (columns etc.) in the SELECT list that are > either not in a GROUP BY clause, or used in an aggregate function when you > use GROUP BY in the statement. By saying SELECT *, means you would have to > GROUP BY gc, co, data, ora ... Doh! Of course. Should have had coffee before writing that ;) > > That isn't going to do what he wants. And the SQL you stated should give you > an error: > > ERROR: column "temp50.co" must appear in the GROUP BY clause or be used in an > aggregate function > > What he wants to do is use DISTINCT ON: > > SELECT DISTINCT ON (gc) gc, co, data, ora FROM temp50 ORDER BY gc, ora DESC; > > Andy >