Re: data versioning ?

From:

Lew <lew@lewscanon.com>

Newsgroups:

comp.lang.java.programmer

Date:

Wed, 14 Jul 2010 07:38:49 -0700 (PDT)

Message-ID:

<97d89067-a41b-4764-888d-d25dee868aee@j8g2000yqd.googlegroups.com>

vlado wrote:

Table COMPANY with columns id, name, value, owner, version and one row
for example :

1 IBM 2.000.000.000 Mark 1

And if the value of the compny change let's say to 2.000.000.001 new row
is inserted in
DB and now it look like this with version raised by one.

1 IBM 2.000.000.000 Mark 1
2 IBM 2.000.000.001 Mark 2

Now my question is how to get only newest(biggest) COMPANY record
referenced to owner Mark.Do I
need some kind of history table but if I do thant I have a lot of
rendundancy ?

Lew wrote:

SELECT co.name, co.value, co.owner, co.version
FROM company co
WHERE co.version =
(SELECT MAX( x.version ) FROM company x
WHERE x.name = co.name AND x.owner = co.owner)
;

Or reading your request a different way, if you want the most recent
company(ies) per owner:

SELECT co.owner, co.name, co.version, co.value
FROM company co
WHERE co.version IN
(SELECT MAX( x.version ) FROM company x
WHERE x.owner = co.owner)
;

Depending on your reporting needs, you can also do clever things with
GROUP BY ... HAVING ...

--
Lew