Re: get only rows for latest version of contents

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