Re: Is there something equivalent to a "Find nearest" in SQL, IB6.0.2.0?

Article View: borland.public.interbase.opensource

Article #164

Back to borland.public.interbase.opensource Previous Next

#164

From: Gerard Vanderveken ghianospam.pi...
Date: Tue, 13 Sep 2005 00:56

22 lines
1820 bytes

nospam_kandw321@icrossroads.com (Red) wrote:
>Dan, there's no "rows" qualifier for the SELECT command in IB 6.0.2.0
>
>...hmm...
>
>

Select * from table1 t 
where
 (t.fld_1 = (Select max(a.fld_1) from table1 a where a.fld_1 <= :lookupvalue)) 
 or 
 (t.fld_1 = (Select min(b.fld_1) from table1 b where b.fld_1 >= :lookupvalue)) 

From the result set (max two records), the program has to select the nearest fld_1 value (look out for and prioritize equal distances).
With a udf you could even
select abs(t.fld1 -:lookupvalue), t.* ... order by 1, fld_1
and then it's always the first record (lowest fld_1 in case of equal difference)

On very large tables you benefit from a fld_1 desc index.

Gerard Vanderveken

Technical Details

Message-ID: <432623b4$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newscon06.news.prodigy.com!prodigy.net!logbridge.uoregon.edu!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

References:

<428a5b61.71058216@newsgroups.borland.com> <428a60f3$1@newsgroups.borland.com> <428a630b.73020187@newsgroups.borland.com> <428a6d3e$1@newsgroups.borland.com> <428b4e07.133176667@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 106772,8899b8536c5e6b11
X-Google-Attributes: gid106772,public
X-Google-Language: ENGLISH,ASCII-7-bit
From: "Gerard Vanderveken" ghia_no_spam.pi.be
Sender: "Gerard Vanderveken" ghia_no_spam.pi.be
Reply-To: "Gerard Vanderveken" ghia_no_spam.pi.be
Subject: Re: Is there something equivalent to a "Find nearest" in SQL, IB6.0.2.0?
Newsgroups: borland.public.interbase.opensource
X-User-Info: 213.49.102.79     
References: <428a5b61.71058216@newsgroups.borland.com> <428a60f3$1@newsgroups.borland.com> <428a630b.73020187@newsgroups.borland.com> <428a6d3e$1@newsgroups.borland.com> <428b4e07.133176667@newsgroups.borland.com>
NNTP-Posting-Host: 207.105.83.66
Message-ID: <432623b4$1@newsgroups.borland.com>
Date: 12 Sep 2005 17:56:20 -0700
X-Trace: newsgroups.borland.com 1126572980 207.105.83.66 (12 Sep 2005 17:56:20 -0700)
Lines: 22
Path: arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newscon06.news.prodigy.com!prodigy.net!logbridge.uoregon.edu!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

Reply to Parent Message