Re: get only rows for latest version of contents

[Erik Jones <erik(at)myemma(dot)com>]

On Oct 25, 2007, at 1:50 AM, Sébastien Meudec wrote:

> Thx a lot Chris.
>
> In fact the correct SQL was (rewritten with inner join because of  
> it is
> required by my api):
>
> select b1.*
> from business b1
> inner join (select idnode,max(version_no) as version_no from business
>             group by idnode) as b2
> on b1.idnode = b2.idnode and
>   (b1.version_no = b2.version_no or b2.version_no is null)
>
> Regards,
> Seb.
>
>
> > -----Message d'origine-----
> > De : pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> > owner(at)postgresql(dot)org] De la part de Christian Kindler
> > Envoyé : mercredi 24 octobre 2007 11:55
> > À : Sébastien Meudec
> > Cc : pgsql-sql(at)postgresql(dot)org
> > Objet : Re: [SQL] get only rows for latest version of contents
> >
> > 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.

Here's another little trick that can come in handy for this:

SELECT DISTINCT ON (Idnode) Idnode, version_no, c1, c2, c3
FROM business
ORDER BY Idnode, version_no DESC;

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com