use recursive common table expressions to find consecutive no.s from two tables

Question

i have the following tables:

Actual         Optional
------         --------
4                 3
13                6
20                7
26                14
                  19
                  21
                  27
                  28

What i have to do is select :

1) all the values from "Actual" Table.

2) select values from "Optional" table if they form a consecutive series with "actual" table values

The expected result is:

Answer
------
4
13
20
26
3    --because it is consecutive to 4 (i.e 3=4-1) 
14   --14=13+1
19   --19=20-1
21   --21=20+1
27   --27=26+1
28   --this is the important case.28 is not consecutive to 26 but 27 
     --is consecutive to 26 and 26,27,28 together form a series.

I wrote a query using recursive cte but it is looping forever and fails after recursion reaches 100 levels. The problem i faced is 27 matches with 26, 28 matches with 27 and 27 with 28.again 28 with 27...(forever)

Here is the query i wrote:

with recurcte as
        (
        select num as one,num as two from actual
        union all
         select opt.num as one,cte.two as two 
         from recurcte cte join optional opt 
         on opt.num+1=cte.one or opt.num-1=cte.one
        )select * from recurcte

this is not homework.This is a small part of a big problem i have.I have written the query for the most part. The problem has to be written in a single query.So thats why a cte stipulation — psy, Sep 09 '11 at 14:21
So you can remove this stipulation about recursive CTEs then? — Martin Smith, Sep 09 '11 at 14:22
Yes, as long it fits into other part of my query,so, on the whole it counts as a single query. — psy, Sep 09 '11 at 14:24
Just a note; the accepted answer *will* perform like a pig on any decent sized sets of data. Where-as Martin Smith's answer will scale much more elegantly. I highly recommend trying both over real-case and worst-case data sets. — MatBailie, Sep 09 '11 at 14:49
@kannan : thanks for testing my query, I've deleted my answer unfortunately have no time yet to improve just now — sll, Sep 09 '11 at 14:55

Martin Smith · Accepted Answer · 2011-09-09T15:01:44.577

6

;WITH Combined
     AS (SELECT 1 AS Actual, N
         FROM   (VALUES(4),
                       (13),
                       (20),
                       (26)) Actual(N)
         UNION ALL
         SELECT 0 AS Actual, N
         FROM   (VALUES(3),
                       (6),
                       (7),
                       (14),
                       (19),
                       (21),
                       (27),
                       (28)) Optional (N)),
     T1
     AS (SELECT *,
                N - DENSE_RANK() OVER (ORDER BY N) AS Grp
         FROM   Combined),
     T2
     AS (SELECT *,
                MAX(Actual) OVER (PARTITION BY Grp) AS HasActual
         FROM   T1)
SELECT DISTINCT N
FROM   T2
WHERE  HasActual = 1

edited Sep 09 '11 at 15:01

answered Sep 09 '11 at 14:32

Martin Smith

438,706
87
741
845

3

@sil not necessarily true if you terminate all statements properly (like you should). Blogged about this two years ago: https://sqlblog.org/blogs/aaron_bertrand/archive/2009/09/03/ladies-and-gentlemen-start-your-semi-colons.aspx – Aaron Bertrand Sep 09 '11 at 14:46
2

And unless it's the first statement in a batch. Which, as shown, it is. – MatBailie Sep 09 '11 at 14:47

Derek · Answer 2 · 2011-09-09T14:39:43.590

1

This CTE will give you the data you're looking for. Recursion is unnecessary for this.

declare @Actual table (i int)
declare @Optional table (i int)

insert into @Actual 
    select 4 union select 13 union select 20 union select 26

insert into @Optional 
    select 3 union select 6 union select 7 union select 14 union select 19
    union select 21 union select 27 union select 28

;with rownum as (
    select *, ROW_NUMBER() OVER (ORDER BY i) as 'RN'
    from (
        select
            i, 'A' as 'Source'
        from
            @Actual
        union
        select
            i, 'O'
        from
            @Optional
    ) a
)

select distinct
    d.i
from
    rownum a
    inner join rownum d
        on  a.i - d.i = a.rn - d.rn
where
    a.source = 'A'

edited Sep 09 '11 at 14:39

answered Sep 09 '11 at 14:28

Derek

21,828
7
53
61

it's worth noting that this approach will get confused if there are duplicate values, whether in one table or in their union – AakashM Sep 09 '11 at 14:36
2

This will become massively inefficient as the dataset grows. As both sides of the `a.i - d.i = a.rn - d.rn` predicate involve both tables, this requires a cross product of the data. Square Laws like this are pretty painful. (Try it for 10 values in each table, then try it for 100, it certainly won't be only 10 times slower.) – MatBailie Sep 09 '11 at 14:39
just added a distinct in the final query which sufficiently handles duplicates, thanks for pointing it out – Derek Sep 09 '11 at 14:40

use recursive common table expressions to find consecutive no.s from two tables

2 Answers2