Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset="iso-8859-1"
Date: Wed, 24 Oct 2007 11:55:06 +0200
From: "Christian Kindler" <christian.kindler@gmx.net>
Message-ID: <20071024095506.292720@gmx.net>
MIME-Version: 1.0
Subject: Re: get only rows for latest version of contents
To: =?iso-8859-1?Q?S=E9bastien_Meudec?= <seb@jack.fr>
Content-Transfer-Encoding: 8bit

Hi!

not quick mut works

select * from business b1
where b1.version_no = (SELECT max(version_no) FROM business b2. 
                        where b2.idnode = b1.idnode
                       )

If you want to make this quiry faster du a regular join

select b1.* 
  from business b1,
       (SELECT max(version_no) FROM business b2. 
         where b2.idnode = b1.idnode
       ) as b2
where b1.idnode = b2.idnode
  and b1.version_no = b2.version_nr

Regards Chris

PS written without running any sql, maybe there are some syntax issues, but i am shure you will figure these out :-)


On Wed, October 24, 2007 11:29 am, Sébastien Meudec wrote:
> 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.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate
> 
-- 
cu
Chris

Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser