From: =?iso-8859-1?Q?S=E9bastien_Meudec?= <seb@jack.fr>
To: <pgsql-sql@postgresql.org>
Subject: get only rows for latest version of contents
Date: Wed, 24 Oct 2007 11:29:53 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcgWIG2ZfGbDx4FhTTKNl3qhCTEITA==
Message-Id: <20071024092414.F2D721C000C4@mwinf2027.orange.fr>

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=20
GROUP BY idnode ORDER BY idnode;

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

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=20
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=E9bastien.