data versioning ?
in [Java Help]
|
Prev: JCaptcha : Doubt about constructor argument of GenericManageableCaptchaService
Next: Korean language broken
From: vlado on 13 Jul 2010 04:52 I have for example table like 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 ? Thanks.
From: Tom Anderson on 13 Jul 2010 07:30 On Tue, 13 Jul 2010, vlado wrote: > I have for example table like > > 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? You also posted this to comp.databases, and that's the right place for it. Look there for answers. Followup-to set. tom -- The players listen very carefully and respectfully to all that the clever men have to say about what is to happen in the next generation. They players then wait until all the clever men are dead, and bury them nicely. Then they go and do something else. -- The Napoleon of Notting Hill, G. K. Chesterton
From: Lew on 13 Jul 2010 17:21 On Jul 13, 4:52 am, vlado <vl...(a)miha.net> wrote: > I have for example table like > > 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 ? > 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) ; -- Lew
From: Lew on 14 Jul 2010 10:38
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 |