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, <<

1. Your question makes no sense in a relational database. A table is a set; a set has NO ordering. NONE. There are no lists in SQL. The concept does not even exist in the relational model.
2. If you want people to help you, then please post DDL, sample data and code.
3. All relationships are shown by values in the columns of rows of tables. This "near" relationship has to be defined by you in terms of the columnhs in your table.

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