just.me just.me - 1 month ago 15
SQL Question

Access value in subquery in view

As an example, I setup a simple SQLite schema like this:

sqlite> CREATE TABLE foo (a TEXT, b INT, PRIMARY KEY(a, b));
sqlite> INSERT INTO foo (a, b) VALUES ('x', 5);
sqlite> INSERT INTO foo (a, b) VALUES ('x', 10);
sqlite> INSERT INTO foo (a, b) VALUES ('x', 15);
sqlite> INSERT INTO foo (a, b) VALUES ('y', 5);
sqlite> INSERT INTO foo (a, b) VALUES ('y', 12);
sqlite> INSERT INTO foo (a, b) VALUES ('y', 16);


b
represents a timestamp and
a
identifies an object. The real application contains more columns, of course.

I would like to create a view which contains a mapping of
(a, b)
to the next smaller value of
b
for which an entry exists in
foo
. It should look like this for the above example:

a | b | previous_b
---------------------
x | 10 | 5
x | 15 | 10
y | 12 | 5
y | 16 | 12


I tried to create a view like this:

CREATE VIEW prev
AS
SELECT
a,
b AS current_b,
(SELECT MAX(b) FROM foo WHERE b < current_b) AS previous_b
FROM foo;


However, it seems like I cannot access
current_b
in the subquery:

sqlite> SELECT * FROM prev;
Error: no such column: current_b


How can I solve this problem? If this is impossible, is there an alternative?

Answer

You can't use aliases like that. Use

CREATE VIEW prev
AS
    SELECT
        f1.a,
        f1.b AS current_b,
        (SELECT MAX(f2.b) FROM foo f2 WHERE f2.b < f1.b) AS previous_b
    FROM foo as f1;