Re: [SQL] how to avoid an inner join

"Harald Fischer-Hohlweck" <Harald.Fischer-Hohlweck_at_ibix.de> wrote in message news:<9o4urg$2c1n$1_at_news.lf.net>...
> Hi.
>
> Assume a table having a key Key and a column ValidFrom (ValidFrom is a
> date). All rows with a definite Key have different ValidFrom's. Can I
> get the row with the youngest ValidFrom in the past (i.e. the row that
> is valid actually) without an inner join? Something like
> select * from table
> where Key = value and ValidFrom = "max(ValidFrom) <= today";
>
> I'm no database expert, but I anticipate performance problems if I have
> to do the inner join.
>
> Sincerely
> Harald

How about this?

select *
from table t1,
  (select key, max(validFrom) latest from table group by key) t2 where t1.validFrom = t2.lastest and t1.key = t2.key

This is still indirectly an inner join, but hopefully Oracle will work out all the rows with the last validFrom per key, which in worst case will only require one full table scan, instead of one per distinct Key value.
Btw, the names you chose for table and column are quite bad even as an example.

hth Received on Mon Sep 17 2001 - 15:48:01 CDT