Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> comp.databases.theory -> Re: Selecting rows near a particular row
>> I want to list items near a particular item in an indexed database,
e.g. if the items are ordered into
a,b,c,d,e,f,g,h,i,j ...
and I am looking for 'f'
then I want the rows representing e,f,g or e,g,h if f does not exist, <<
CREATE TABLE Foobar
(x CHAR(1) NOT NULL PRIMARY KEY);
INSERT INTO Foobar VALUES ('a'); INSERT INTO Foobar VALUES ('b'); INSERT INTO Foobar VALUES ('c'); INSERT INTO Foobar VALUES ('d'); INSERT INTO Foobar VALUES ('e'); INSERT INTO Foobar VALUES ('f'); INSERT INTO Foobar VALUES ('g'); INSERT INTO Foobar VALUES ('h'); INSERT INTO Foobar VALUES ('i');
If we define "near" in LOGICAL terms (not in PHYSICAL terms), then I assume you mean LUB and GLB (math terminology, look it up). Here is one way to do it.
SELECT X1.lft, X1.mid, X2.rgt
FROM
(SELECT MAX(F1.x) AS lft, :x AS mid
FROM Foobar AS F1
WHERE F1.x < :x)AS X1(lft, mid)
INNER JOIN
(SELECT :x AS mid, MIN(F2.x) AS rgt
FROM Foobar AS F2
WHERE :x < F2.x) AS X2(mid, rgt)
ON X1.mid = X2.mid
I am putting out a NULL for missing values. You did not say what to do with values that are not in the table (i.e. SET :x = 'z';), so I have no idea what you want.
I think you need to read a book on relational basics. Received on Thu May 23 2002 - 12:28:12 CDT