About Join

Database
Enthusiast

About Join

Hi All!
We have a table with sequences of records, which need to be merged into one record if the difference between them is 1. Let's have a sample table with data as below, then we expect to return 2 rows instead of original 4. Wonder about some recursion, but have no experience with it.

Thank you for your help

Petr

CREATE TABLE DB.T1
AS (
a INT NOT NULL
, b INT NOT NULL
) PRIMARY INDEX (a)
;

INSERT INTO DB.T1 VALUES(1,2);
INSERT INTO DB.T1 VALUES(3,4);
INSERT INTO DB.T1 VALUES(5,6);
INSERT INTO DB.T1 VALUES(8,9);

Expected returned values (a, b) after merge are:
1, 6
8, 9
3 REPLIES
Enthusiast

Re: About Join

i am not able to understand ur question?
if u give me table a and table b , i will try to solve the prob?
Senior Apprentice

Re: About Join

Hi Petr,
this is a "find the gap" question:

SELECT MIN(a), MAX(b)
FROM
(
SELECT
a,b,
SUM(change_indicator)
OVER (ORDER BY b ROWS UNBOUNDED PRECEDING) AS grp
FROM
(
SELECT a,b,
CASE WHEN a - MIN(b)
OVER (ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = 1
THEN 0
ELSE 1 END AS change_indicator -- difference of current and previous value <> 1?
FROM t1
) AS dt
) AS dt
GROUP BY grp

This query might be further simplified to two nested OLAP functions without the aggregate, but it's too late for me now :-)

Dieter
Enthusiast

Re: About Join

Hi Dieter!
Thank you a lot. That's exactly, what I was looking for.

Best Regards

Petr