From: Sébastien 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
Message-ID: <20071024092414.F2D721C000C4@mwinf2027.orange.fr> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: seb@jack.fr
Subject: Re: get only rows for latest version of contents
In-Reply-To: <20071024092414.F2D721C000C4@mwinf2027.orange.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox