SQL Query getting on row where a = 1 the id of the next row where b = 1

user_15505651 0
Viewed 151

i have this test sample.

WITH test AS(
SELECT 1 AS id, "0" AS a, "0" AS b
UNION SELECT 2 AS id, "0" AS a, "1" AS b
UNION SELECT 3 AS id, "0" AS a, "0" AS b
UNION SELECT 4 AS id, "1" AS a, "0" AS b
UNION SELECT 5 AS id, "0" AS a, "0" AS b
UNION SELECT 6 AS id, "0" AS a, "1" AS b
UNION SELECT 7 AS id, "0" AS a, "1" AS b
UNION SELECT 8 AS id, "1" AS a, "0" AS b
UNION SELECT 9 AS id, "0" AS a, "1" AS b
UNION SELECT 10 AS id, "0" AS a, "0" AS b
),
test_a AS (
SELECT id FROM test WHERE a = "1"),
test_b AS (
SELECT id FROM test WHERE b = "1")

I need every row where a = 1 (thats simple) with the id of the next row where b = 1

The result should be:

id_a id_b
4 6
8 9

my only working solution is by joining all rows where id is greater and b = 1 to the original data, set a row number and keep only row_nr 1 but with billions of data this is very slow.

Hope you can get my point. Thanks for any help.

Edit:

Just tried this code, but i get the error below. If i use B.id = A.id then i got a result, but its not what i need.

SELECT   A.id AS id_a,
         (SELECT min(id) FROM test_b B WHERE B.id < A.id) AS id_b
FROM     test_a A
ORDER BY A.id

Error running query
Correlated column is not allowed in a non-equality predicate:
azure-databricks sql
2 Answers

Example:

select 
    aa.id as id_a, 
    (select id from test where b = 1 and id > aa.id limit 1) as id_b
from test aa 
where aa.a = 1 
order by aa.id
user_172960840

Now after a while, i got a solution, working with databricks sql. I'm using the last_value function with "ignore null" (i think its also available for other sql engines)

WITH test AS(
SELECT 1 AS id, "0" AS a, "0" AS b
UNION SELECT 2 AS id, "0" AS a, "1" AS b
UNION SELECT 3 AS id, "0" AS a, "0" AS b
UNION SELECT 4 AS id, "1" AS a, "0" AS b
UNION SELECT 5 AS id, "0" AS a, "0" AS b
UNION SELECT 6 AS id, "0" AS a, "1" AS b
UNION SELECT 7 AS id, "0" AS a, "1" AS b
UNION SELECT 8 AS id, "1" AS a, "0" AS b
UNION SELECT 9 AS id, "0" AS a, "1" AS b
UNION SELECT 10 AS id, "0" AS a, "0" AS b
),
test_a AS (
SELECT id FROM test WHERE a = "1"),
test_b AS (
SELECT id FROM test WHERE b = "1"),
union as (
select id as id, NULL as id_b, 0 as t from test_a
union
select id as id, id as id_b, 1 as t from test_b
),
missing_data as (
SELECT id , last_value(id_b, true) over (order by id DESC) id_b, t FROM union 
)

SELECT id, id_b  FROM missing_data where t = 0
user_155056510
Related