Comparing data in two (or more) ranges in the same field

Question

I have a simple table as follows

SQL> select * from test;

        ID STUFF
---------- ------------------------------------------------------------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f
         7 g

7 rows selected.

I'd like to construct a query that returns something like this:

STUFF A    STUFF B
---------- --------------------------------------
a          e
b          f
c          g
d          NULL

That is, take two ranges determined by the id, with missing values padded by NULL. The ranges are continuous, may overlap, and are different lengths.

Is this possible? If so, what's the query?

---

Temp table sql:

CREATE TABLE test(id number, stuff VARCHAR(20));
INSERT INTO test VALUES (1, 'a');
INSERT INTO test VALUES (2, 'b');
INSERT INTO test VALUES (3, 'c');
INSERT INTO test VALUES (4, 'd');
INSERT INTO test VALUES (5, 'e');
INSERT INTO test VALUES (6, 'f');
INSERT INTO test VALUES (7, 'g');

Answer 1

select a.stuff as stuffa, b.stuff as stuffb
from test as a
left join test as b
  on (a.id-:minida) = (b.id-:minidb)
  and b.id between :minidb and :maxidb
where a.id between :minida and :maxida

(where the colon denoted identifiers for values that get bound to a prepared statement) should work if (maxidb-minidb) <= (maxida-minida). But this doesn't work in a completely symmetrical way (where either range may be larger than the other).

A totally symmetrical query as you describe could no doubt be written as a remarkably tedious UNION which basically repeats each part (with appropriate swapping) and adds the above <= expression as a condition the first time, same but with > instead the second time (asymmetrically, in case the ranges are equal;-), so that one of the two halves of the union is guaranteed to be empty, but I'd have to be seriously compensated for the tedium of actually writing out said union;-).

If your favorite SQL dialect supports FULL OUTER JOIN then that could help... but many dialects, such as MySQL and SQLite, do not support the FULL version.

Answer 2

I'm having a hard time wrapping my head around what you're trying to do in general. But this statement will work on the example you gave. Hopefully that points you in the right direction for the more generic solution.

select stuff as stuffa,(select top 1 stuff from test where id >= t.id + 4 )stuffb from test t where t.Id<=4

Answer 3

@Alex Nice work. I echo your concerns for the tedium in generating symmetric results. Don't bother. If it's going to become complex for this simple example, it will become a nightmare for production code (not to mention the possibility of comparing more than two ranges).

@tetra Basically any valid range for id. The lengths and position of the ranges are unknown until deployment.

@Everyone A neat solution is preferred, and "it can't be done neatly" is a fine solution. I can tell the client, 'it can't be done'.