Re:

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>