get only rows for latest version of contents - Mailing list pgsql-sql

From Sébastien Meudec
Subject get only rows for latest version of contents
Date
Msg-id 20071024092414.F2D721C000C4@mwinf2027.orange.fr
Whole thread Raw
List pgsql-sql
Hi everybody.

I have a table like that (i simplified it):
CREATE TABLE business { idnode integer not null, version_no integer, c1 text, c2 text, c3 text
}
With a unique index in (idnode,version_no).

This table records many version from contents identified by idnode where
texts may be different.
So i can have:
Idnode | version_no | c1    | c2    | c3
111    | 2          | foo1  | foo2  | foo3
111    | 1          | fee1  | foo2  | foo3
111    | null       | fee1  | fee2  | fee3
222    | null       | too1  | too2  | too3
333    | 1          | xoo1  | xoo2  | xoo3
333    | null       | yoo1  | yoo2  | yee3

I want to select all columns but only for last (greatest) version of each
content. So I want a result like:
Idnode | version_no | c1    | c2    | c3
111    | 2          | foo1  | foo2  | foo3
222    | null       | too1  | too2  | too3
333    | 1          | xoo1  | xoo2  | xoo3

If i do:
SELECT idnode, max(version_no) FROM business
GROUP BY idnode ORDER BY idnode;

I get effectively only last version:
Idnode | version_no
111    | 2
222    | null
333    | 1

But as soon that i want to get texts, I don't know how to build the SQL.
In each SQL i tested i've been forced to put text column in a group by since
i used aggregate for version_no:
SELECT idnode, max(version_no), c1, c2, c3 FROM BUSINESS
GROUP BY idnode, c1, c2, c3 ORDER BY idnode;

But with that SQL, because of the group by and different values in text i
get
Idnode | version_no | c1    | c2    | c3
111    | 2          | foo1  | foo2  | foo3
111    | 1          | fee1  | foo2  | foo3
111    | null       | fee1  | fee2  | fee3
222    | null       | too1  | too2  | too3
333    | 1          | xoo1  | xoo2  | xoo3
333    | null       | yoo1  | yoo2  | yee3

As we can't do aggregate in join neither in where, i can't get what i want.

Anybody could help me to build proper SQL ?

Thx for your answers.
Sébastien.




pgsql-sql by date:

Previous
From: Harald Fuchs
Date:
Subject: Re: request for help with COPY syntax
Next
From: "Christian Kindler"
Date:
Subject: Re: get only rows for latest version of contents