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 ? How to do it ?

On Tue, 13 Jul 2010, vlado wrote:

This thread:

http://groups.google.com/group/comp....f70888f00f0c31

May be of some use to you, although what you're doing is a simpler
version, where you only need information from one group, not from each
group.

I think you can write:

select *
from company c
where c.owner = 'Mark'
and c.version = (
select max(version)
from company c2
where c2.owner = 'Mark'
)

If you know that there will never be more than one record matching the
version number, then you can drop the "where c.owner = 'Mark".

If you wanted to do everyone at once, you could do:

select *
from company c
where c.version = (
select max(version)
from company c2
where c2.owner = c.owner
)

I'm more confident about that query than about the first one, actually!

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

In article <i1h91s$t6p$1 (AT) ss408 (DOT) t-com.hr>, vlado (AT) miha (DOT) net says...

There may be more efficient ways, but one brute force method
(UNTESTED!!) would be:

select * from company where value = (select max(value) from company
where owner = 'Mark')

You'll want indexes on owner and value if your table is of any size.

D