Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 527
psy Avatar asked Sep 09 '11 14:09

psy


People also ask

What is recursive common table expressions?

A recursive common table expression (CTE) is a CTE that references itself. By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the complete result set.

Can I use 2 CTE in SQL?

The first CTE is separated from the second one by a comma. This also goes if you write more than two CTEs: all the CTEs are separated by a comma. However, no matter how many CTEs you have, there's no comma between the last CTE and the main query.

What are common table expressions CTE )? What is recursive CTE in which situation have you used it if you want to limit recursion How can you do that in CTE?

What are CTEs? The CTE (common table expression), also known as the WITH clause, is an SQL feature that returns a temporary data set that can be used by another query. As it's a temporary result, it's not stored anywhere, but it still can be referenced like you would reference any other table.

Can you use a CTE within a CTE?

Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.


2 Answers

;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  
like image 117
Martin Smith Avatar answered Nov 10 '22 03:11

Martin Smith


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'
like image 44
Derek Kromm Avatar answered Nov 10 '22 03:11

Derek Kromm