It's a simple select/group by:
select nid,type,max(version) from node group by nid,type;
It'd help reading the postgresql SQL tutorial to freshen you up.
cheers,
thalis
On Sun, 7 Oct 2001, J C Lawrence wrote:
>
> 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>