[SQL] how to avoid an inner join

Article View: bit.listserv.sqlinfo

Article #295

Back to bit.listserv.sqlinfo Previous Next

#295

From: Harald Fischer-Hohlweck
Date: Mon, 17 Sep 2001 13:46

21 lines
1802 bytes

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

--
Harald Fischer-Hohlweck      mailto:Harald.Fischer-Hohlweck@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

Technical Details

Message-ID: <9o4ura$2c1m$1@news.lf.net>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!newsfeed.google.com!newsfeed.stanford.edu!news.tele.dk!small.news.tele.dk!195.54.122.107!newsfeed1.bredband.com!bredband!newsfeed1.telenordia.se!news.algonet.se!algonet!f.de.uu.net!news.uni-stuttgart.de!news.belwue.de!LF.net!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 11355a,1f08830fb4c5f534,start
X-Google-Attributes: gid11355a,public
X-Google-ArrivalTime: 2001-09-17 06:52:40 PST
Path: arc1-ca.pugleaf.net!archiver1.google.com!newsfeed.google.com!newsfeed.stanford.edu!news.tele.dk!small.news.tele.dk!195.54.122.107!newsfeed1.bredband.com!bredband!newsfeed1.telenordia.se!news.algonet.se!algonet!f.de.uu.net!news.uni-stuttgart.de!news.belwue.de!LF.net!not-for-mail
From: "Harald Fischer-Hohlweck" <Harald.Fischer-Hohlweck@ibix.de>
Newsgroups: bit.listserv.sqlinfo
Subject: [SQL] how to avoid an inner join
Date: Mon, 17 Sep 2001 15:46:16 +0200
Organization: LF.net GmbH, Stuttgart, Germany
Lines: 24
Message-ID: <9o4ura$2c1m$1@news.lf.net>
NNTP-Posting-Host: brick.ibix.de
X-Trace: news.lf.net 1000734378 77878 212.9.176.132 (17 Sep 2001 13:46:18 GMT)
X-Complaints-To: usenet@news.lf.net
NNTP-Posting-Date: Mon, 17 Sep 2001 13:46:18 +0000 (UTC)
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400