Re: [SQL] how to avoid an inner join

Hi

"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.
>

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

-- 
Posted from  [212.20.131.226] 
via Mailgate.ORG Server - http://www.Mailgate.ORG