I have the following data, by group
, each row having an increasing index
, and containing a limit
and a value
:
CREATE TABLE items ("group" VARCHAR, index INTEGER, "limit" INTEGER, value INTEGER) ;
INSERT INTO items ("group", index, "limit", value) VALUES
('A', 1, 2, 1), ('A', 2, NULL, 3),
('B', 1, 3, 2), ('B', 2, NULL, 2), ('B', 3, 1, 3), ('B', 4, 2, 5),
('C', 1, 2, 3), ('C', 2, 2, 3), ('C', 3, NULL, 4), ('C', 4, 5, 5) ;
group | index | limit | value
-------+-------+-------+-------
A | 1 | 2 | 1
A | 2 | | 3
B | 1 | 3 | 2
B | 2 | | 2
B | 3 | 1 | 3
B | 4 | 2 | 5
C | 1 | 2 | 3
C | 2 | 2 | 3
C | 3 | | 4
C | 4 | 5 | 5
For each row, I want to know the next
index
from the same group
whose value
is stricly above the limit
of the current row, i.e.:
group | index | limit | value | next
-------+-------+-------+-------+------
A | 1 | 2 | 1 | 2
A | 2 | | 3 |
B | 1 | 3 | 2 | 4
B | 2 | | 2 |
B | 3 | 1 | 3 | 4
B | 4 | 2 | 5 |
C | 1 | 2 | 3 | 4
C | 2 | 2 | 1 | 4
C | 3 | | 1 |
C | 4 | 5 | 5 |
Question: how to achieve this with window functions? I thought about the following query, but I do not know how to specify in my CASE
that limit
refers to the current row while value
refers to the frame row:
SELECT *,
MIN(CASE WHEN value < "limit" THEN index ELSE NULL END)
OVER (PARTITION by "group" ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next
FROM items
Thanks in advance for any help!