From: =?iso-8859-1?Q?S=E9bastien_Meudec?= <seb@jack.fr>
To: "'Christian Kindler'" <christian.kindler@gmx.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: get only rows for latest version of contents
Date: Thu, 25 Oct 2007 08:50:55 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-reply-to: <20071024095506.292720@gmx.net>
Thread-Index: AcgWJGDgV4gY7sIVSfOVBvXFWbBLewAroQSQ
Message-Id: <20071025064454.B6D3A1C00086@mwinf2727.orange.fr>

Thx a lot Chris.

In fact the correct SQL was (rewritten with inner join because of it is
required by my api):

select b1.*=20
from business b1
inner join (select idnode,max(version_no) as version_no from business
            group by idnode) as b2
on b1.idnode =3D b2.idnode and=20
  (b1.version_no =3D b2.version_no or b2.version_no is null)

Regards,
Seb.


> -----Message d'origine-----
> De=A0: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] De la part de Christian Kindler
> Envoy=E9=A0: mercredi 24 octobre 2007 11:55
> =C0=A0: S=E9bastien Meudec
> Cc=A0: pgsql-sql@postgresql.org
> Objet=A0: Re: [SQL] get only rows for latest version of contents
>=20
> Hi!
>=20
> not quick mut works
>=20
> select * from business b1
> where b1.version_no =3D (SELECT max(version_no) FROM business b2.
>                         where b2.idnode =3D b1.idnode
>                        )
>=20
> If you want to make this quiry faster du a regular join
>=20
> select b1.*
>   from business b1,
>        (SELECT max(version_no) FROM business b2.
>          where b2.idnode =3D b1.idnode
>        ) as b2
> where b1.idnode =3D b2.idnode
>   and b1.version_no =3D b2.version_nr
>=20
> Regards Chris
>=20
> PS written without running any sql, maybe there are some syntax =
issues,
> but i am shure you will figure these out :-)
>=20
>=20
>=20
> On Wed, October 24, 2007 11:29 am, S=E9bastien 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=E9bastien.
> >
> >
> >
> > ---------------------------(end of =
broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project by donating at
> >
> >                 http://www.postgresql.org/about/donate
> >
> --
> cu
> Chris
>=20
> Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
> Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
>=20
> ---------------------------(end of =
broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>=20
>                 http://www.postgresql.org/about/donate