Hi again, Pierre!

You're right that my code gives the latest version before :FOR_VERSION. If you e.g. want the latest two product versions, but only the latest family, you could do something like:

select
p.PRD_ID,
p.PRD_VERSION,
p.PRD_NAME,
p.PRD_FAMILY,
f.FAM_NAME,
from PRODUCTS p
join FAMILIES f on f.FAM_ID = p.PRD_FAMILY
where p.PRD_VERSION <= :FOR_VERSION
and not exists (
select * from PRODUCTS p2
join PRODUCTS p3 on p3.PRD_ID = p2.PRD_ID
and p3.PRD_VERSION < p2.PRD_VERSION
where p2.PRD_ID=p.PRD_ID
and p2.PRD_VERSION <= :FOR_VERSION
and p3.PRD_VERSION > p.PRD_VERSION
)
and not exists (
select * from FAMILIES f2
where f2.FAM_ID=f.FAM_ID
and f2.FAM_VERSION <= :FOR_VERSION
and f2.FAM_VERSION > f.FAM_VERSION
)

Does this answer anything, or didn't I understand your question?

I find Philippes solution simple to read, it just lacks a GROUP BY on all fields that aren't aggregate (though if PRD_ID and PRD_VERSION is the primary key, then I don't understand why you have a PRODUCTS p2 at all). In the solution below, I've also included FAMILIES:

select
p.PRD_ID,
max(p.PRD_VERSION) as PRD_VERSION,
max(p2.PRD_NAME) as PRD_NAME,
max(p2.PRD_FAMILY) as PRD_FAMILLY,
f.FAM_NAME,
from PRODUCTS p
join PRODUCTS p2 on (
p.PRD_ID = p2.PRD_ID and p.PRD_VERSION = p2.PRD_VERSION
)
join FAMILIES f on f.FAM_ID = p.PRD_FAMILY
where p.PRD_VERSION <= :FOR_VERSION
GROUP BY p.PRD_ID, f.FAM_NAME

In theory, the two queries are slightly different (if we forget p3) - "my" query would return multiple rows if there were several records for the same PRD_ID and PRD_VERSION, whereas "Philippe" would return only one row which could contain values from several records. Normally, I would use my solution if I wanted an updatable query (I'm using IBO, which can do this easily with my query), whereas I'd say Philippes query is easier to read (for queries, I think that's more important than being sexy) if you just want to know the maximum values for some of the fields.

HTH,
Set

> Hi Svein,
>
>> Problem 1 - efficiency:
>>
>> See if something similar to this procedure is any quicker (it won't
>> work, since you are referring to the alias 'a', which isn't
>> mentioned anywhere):
>
> Sorry for the "a" aliased table, congratulations for having found out
> something useable from such a... stupid statement.
>
> Anyway, thank you very much for your answer, your request is one of
> the more efficient I, and people who helpe me found :
>
> select
> p.PRD_ID,
> p.PRD_VERSION,
> p.PRD_NAME,
> p.PRD_FAMILY,
> f.FAM_NAME,
> from PRODUCTS p
> join FAMILIES f on (f.FAM_ID = p.PRD_FAMILY)
> where not exists (
> select * from PRODUCTS p2
> where p2.PRD_ID=p.PRD_ID
> and p2.PRD_VERSION <= :FOR_VERSION
> and p2.PRD_VERSION > p.PRD_VERSION
> )
> and not exists (
> select * from FAMILIES f2
> where f2.FAM_ID=f.FAM_ID
> and f2.FAM_VERSION <= :FOR_VERSION
> and f2.FAM_VERSION > f.FAM_VERSION
> )
>
> There's just a little "bug" : I cannot retrieve another "old" version
> of the database without adding a test on PRD_VERSION, your code can
> only give me the latest version :
>
> select
> p.PRD_ID,
> p.PRD_VERSION,
> p.PRD_NAME,
> p.PRD_FAMILY,
> f.FAM_NAME,
> from PRODUCTS p
> join FAMILIES f on (f.FAM_ID = p.PRD_FAMILY)
> where
>
> /* Here */
>
> (p.PRD_VERSION <= :FOR_VERSION)
>
> /* /Here */
>
> and not exists (
> select * from PRODUCTS p2
> where p2.ART_ID=a.ART_ID and p2.ART_VERSION > p.ART_VERSION
> and p2.ART_VERSION <= :FOR_VERSION
> )
> and not exists(
> select * from FAMILIES f2
> where f2.FAM_ID=f.FAM_ID and f2.FAM_VERSION > f.FAM_VERSION
> and f2.FAM_VERSION <= :FOR_VERSION
> )
>
> I have had another suggestion by Philippe that is the most efficient,
> but I don't like it very much because it is not... sexy and I did'nt
> find how to use it for joined tables :
>
> select
> p.PRD_ID,
> max(p.PRD_VERSION) as PRD_VERSION,
> max(p2.PRD_NAME) as PRD_NAME,
> max(p2.PRD_FAMILY) as PRD_FAMILLY,
> from PRODUCTS p
> join PRODUCTS p2 on (
> p.PRD_ID = p2.PRD_ID and p.PRD_VERSION = p2.PRD_VERSION
> )
> where p.PRD_VERSION <= :FOR_VERSION
>
> It would be nice to find a way to write it without 'maxes' because
> it's easier to read.
>
> Regards,
>
> --
> Pierre Y.

[Non-text portions of this message have been removed]