Re: [SQL] how to avoid an inner join

"Thomas Schwickert" <schwickert_at_yahoo.com> schrieb im Newsbeitrag news:fc1cd77860385d8a80ba4c5f873dc71e.20305_at_mygate.mailgate.org...
>
> "Harald Fischer-Hohlweck" <Harald.Fischer-Hohlweck_at_ibix.de> wrote in
message
> news:9o4urg$2c1n$1_at_news.lf.net...
>
> > 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.
>
> select key,datum from xxx o
> where key=1
> and datum =
> (select max(datum)
> from xxx i
> where i.key=o.key)
>
> there should be indexes on k, perhaps on (k,datum)
>
> hth
> Thomas

Thank you Thomas, but this is what I meant to be an inner join. Perhaps I`m not accurate enough: I hoped the "max(ValidFrom) <= today" could be formulated without a select.

To me this problem - in general: get the _last/first_ row of a set fulfilling a condition - seems to be so frequent, that I guess there should be some function or what ever to do this. Albeit it would be some contradiction to SQL designed to query _sets_ of rows, as I concede.

Still I'm engaged in this problem. If someone knows an answer at least for oracle (>= 8.1) it would be appreciated!

Thank you.
Harald

--
Harald Fischer-Hohlweck      mailto:Harald.Fischer-Hohlweck_at_ibix.de

IBIX Informationssysteme GmbH  http://www.ibix.de
Vor dem Lauch 19               fon : +49-711-7256-324
70567 Stuttgart (Germany)      fax : +49-711-7256-414