Thread: get only rows for latest version of contents

get only rows for latest version of contents

From
Sébastien Meudec
Date:
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.




Re: get only rows for latest version of contents

From
"Christian Kindler"
Date:
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


Re: get only rows for latest version of contents

From
Sébastien Meudec
Date:
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@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] De la part de Christian Kindler
> Envoyé : mercredi 24 octobre 2007 11:55
> À : Sébastien Meudec
> Cc : pgsql-sql@postgresql.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.
> >
> >
> >
> > ---------------------------(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate




Re: get only rows for latest version of contents

From
Erik Jones
Date:
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@postgresql.org [mailto:pgsql-sql-
>> owner@postgresql.org] De la part de Christian Kindler
>> Envoyé : mercredi 24 octobre 2007 11:55
>> À : Sébastien Meudec
>> Cc : pgsql-sql@postgresql.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@myemma.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




Re: get only rows for latest version of contents

From
Sébastien Meudec
Date:
Hi Erik

Thx for your suggestion.
I've done some tests and correct the order to get what i want.

In fact the order i would like to have is 3,2,1,null (null is a draft
version on my api).
But the order ASC gives 1,2,3,null
And the order DESC gives null,3,2,1
So i use order by -(version_no) that gives me 3,2,1,null

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

But finally, when i have wanted to inject this way in my real big request it
gives me error:
SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Because my api requires a different order and it seems idnode should be in
first of the order to be able to use DISTINCT ON.

So for now, i'll keep Chris solution.
I have not profile it with large tables but it's the only solution i have
for now ;)

Thx everybody.
Regards
Sebastien


> -----Message d'origine-----
> De : Erik Jones [mailto:erik@myemma.com]
> Envoyé : jeudi 25 octobre 2007 16:35
> À : Sébastien Meudec
> Cc : 'Christian Kindler'; pgsql-sql@postgresql.org
> Objet : Re: [SQL] 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@postgresql.org [mailto:pgsql-sql-
> >> owner@postgresql.org] De la part de Christian Kindler
> >> Envoyé : mercredi 24 octobre 2007 11:55
> >> À : Sébastien Meudec
> >> Cc : pgsql-sql@postgresql.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@myemma.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
>