Hi PPL,

i need help for following problem.
my table (actual date is 2007/sysdate):

name | date_from | date_to | Version
================================================== ===
N1 2000 2008 1
N1 2002 2008 2 <---------
N1 2008 2010 3
N2 2001 2002 1
N2 2002 2010 2 <---------

id like to get all sets with:
1. actual date is between inside data_from and date_to
AND
2. highest version

so, the result for my table is (every name occurs once!):
N1 2002 2008 2
N2 2002 2010 2

Has anybody an idea for a SQL query??
Thanks!
Mike

assumes Version is distinct within name

hi r937,

it works very fine but I should describe my problem otherwise :

the result of:
1. actual date is between inside data_from and date_to
example:
[name] [date_from] [date_to] [version]
a_______2001_______2010_____1____
a_______2002_______2008_____9____
b_______2000_______2009_____1____


should be used in:
2. highest version
end result:
[name] [date_from] [date_to] [version]
a_______2002_______2008_____9____
b_______2000_______2009_____1____

So we have two steps while the first one has higher prioraty.
PS: I use Oracle9.2

Thanks
Mike

two things:

first, if you wanted an oracle solution, you should've posted in the oracle forum, duh

second, did you try my query at all?

Your step 1 is implemented by the WHERE condition of r937's subquery.
Your step 2 is implemented by the MAX expression in the SELECT subclause of that subquery, which is effectively executed after the WHERE subclause.
Finally, there is a step 3, viz. the retrieval of the full lines that correspond to the combined [name],[MAX(version)] of the subquery.
This is done in the outer query.