From: J C Lawrence <claw(at)kanga(dot)nu>
To: pgsql-general(at)postgresql(dot)org
Subject:
Date: 2001-10-08 06:51:52
Message-ID: 10793.1002523912@kanga.nu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Given a table ala:

CREATE TABLE "node" (
"nid" integer NOT NULL
"type" integer NOT NULL;
"version" integer NOT NULL,
);

Where version defines is the count of the number of revisions to a
given nid/type tuple.

How could I construct a query which extracts only the rows with the
largest version number for each nid/type combination? eg given:

nid type version
---------------------
1 1 5
1 1 4
1 1 3
1 1 2
1 1 1
2 3 2
2 3 1
3 7 4
3 7 3
3 7 2
3 7 1

I want a query which will return:

nid type version
---------------------
1 1 5
2 3 2
3 7 4

Is there a way without doing a temporary table and doing a bunch of
SELECT INTOs>

--
J C Lawrence
---------(*) Satan, oscillate my metallic sonatas.
claw(at)kanga(dot)nu He lived as a devil, eh?
http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.

Responses

  • Re: at 2001-10-08 14:09:47 from Thalis A. Kalfigopoulos

Browse pgsql-general by date

  From Date Subject
Next Message Andrej Falout 2001-10-08 08:00:22 [Announce] Aubit 4gl, Informix-4gl compatible OpenSource GNU compiler project
Previous Message Kelly Harmon 2001-10-08 03:32:22 Accessing Database files on a "read-only" medium...like a CD.