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 : [EMAIL PROTECTED] [mailto:pgsql-sql-
[EMAIL PROTECTED] 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®
[EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
