Forum Stats

  • 3,733,252 Users
  • 2,246,738 Discussions
  • 7,856,634 Comments

Discussions

kind of sequential query

MichaelS
MichaelS Member Posts: 8,424 Bronze Crown
edited June 2007 in SQL & PL/SQL
Hello folks,

my requirement is: Given a number N (let's say 7 for this example) generate a query with following output:
SELECT A,B FROM some_query
/

A  B
-------
1  7
1  7
1  7
1  7
1  7
1  7
1  7
2  6
2  6
2  6
2  6
2  6
2  6
3  5
3  5
3  5
3  5
3  5
4  4
4  4
4  4
4  4
5  3
5  3
5  3
6  2
6  2
7  1

28 rows selected.
Any ideas?

Comments

  • Muthukumar S
    Muthukumar S Member Posts: 711
    simple workaround stricks my mind is that go for creating some functions.

    or try hierarchy queries with connect by sys clause
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Michaels,

    Here a try :
    SQL> select c1, c2
    2 from (select decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) c1,
    3 myvalue-decode(mod(rownum,myvalue),0,myvalue-1,mod(rownum,myvalue)-1) c2,
    4 row_number() over (partition by decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) order by null) rn
    5 from (select 7 myvalue from dual)
    6 connect by level <= myvalue*myvalue)
    7 where c2>=rn
    8 order by c1
    9 /

    C1 C2
    ---------- ----------
    1 7
    1 7
    1 7
    1 7
    1 7
    1 7
    1 7
    2 6
    2 6
    2 6
    2 6
    2 6
    2 6
    3 5
    3 5
    3 5
    3 5
    3 5
    4 4
    4 4
    4 4
    4 4
    5 3
    5 3
    5 3
    6 2
    6 2
    7 1

    28 rows selected.
    Nicolas.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Incredible! Thanks a lot Nicolas.

    Regards,
    Michael
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    You're welcome !

    Nicolas.
  • mennan
    mennan Member Posts: 541
    One other alternative may be:
    SQL> SELECT t3.i, t3.k
      2    FROM (SELECT t1.i, t1.k, trunc((rownum - 1) / 7) + 1 d, rownum r
      3            FROM (SELECT LEVEL i, 8 - LEVEL k FROM dual CONNECT BY LEVEL < 8) t1
      4                ,(SELECT dummy FROM dual CONNECT BY LEVEL < 8) t2) t3
      5   WHERE t3.i + t3.r <= ( 7 * t3.d) + 1;
    
             I          K
    ---------- ----------
             1          7
             1          7
             1          7
             1          7
             1          7
             1          7
             1          7
             2          6
             2          6
             2          6
             2          6
             2          6
             2          6
             3          5
             3          5
             3          5
             3          5
             3          5
             4          4
             4          4
             4          4
             4          4
             5          3
             5          3
             5          3
             6          2
             6          2
             7          1
    
    28 rows selected
    
    SQL> 
    More generic
    SELECT t3.i, t3.k
      FROM (SELECT t1.i, t1.k, trunc((rownum - 1) / :base_number) + 1 d, rownum r
              FROM (SELECT LEVEL i, :base_number + 1 - LEVEL k FROM dual CONNECT BY LEVEL < :base_number + 1) t1
                  ,(SELECT dummy FROM dual CONNECT BY LEVEL < :base_number + 1) t2) t3
     WHERE t3.i + t3.r <= ( :base_number * t3.d) + 1;
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Thanks too mennan.
    But ...arghh .... I have to modify my initial output again to read:
    A  B
    ------
    1  1
    1  2
    1  3
    1  4
    1  5
    1  6
    1  7
    2  1
    2  2
    2  3
    2  4
    2  5
    2  6
    3  1
    3  2
    3  3
    3  4
    3  5
    4  1
    4  2
    4  3
    4  4
    5  1
    5  2
    5  3
    6  1
    6  2
    7  1
    Any help is kindly appreciated.

    Regards,
    Michael
  • mennan
    mennan Member Posts: 541
    SQL> SELECT t3.i, t3.k, t3.j
      2    FROM (SELECT t1.i, t1.k, t2.j, trunc((rownum - 1) / 7) + 1 d, rownum r
      3            FROM (SELECT LEVEL i, 8 - LEVEL k FROM dual CONNECT BY LEVEL < 8) t1
      4                ,(SELECT LEVEL j FROM dual CONNECT BY LEVEL < 8) t2) t3
      5   WHERE t3.i + t3.r <= (7 * t3.d) + 1;
    
             I          K          J
    ---------- ---------- ----------
             1          7          1
             1          7          2
             1          7          3
             1          7          4
             1          7          5
             1          7          6
             1          7          7
             2          6          1
             2          6          2
             2          6          3
             2          6          4
             2          6          5
             2          6          6
             3          5          1
             3          5          2
             3          5          3
             3          5          4
             3          5          5
             4          4          1
             4          4          2
             4          4          3
             4          4          4
             5          3          1
             5          3          2
             5          3          3
             6          2          1
             6          2          2
             7          1          1
    
    28 rows selected
    
    SQL> 
    
    
    SELECT t3.i, t3.k, t3.j
      FROM (SELECT t1.i, t1.k, t2.j, trunc((rownum - 1) / :base_number) + 1 d, rownum r
              FROM (SELECT LEVEL i, :base_number + 1 - LEVEL k FROM dual CONNECT BY LEVEL < :base_number + 1) t1
                  ,(SELECT LEVEL j FROM dual CONNECT BY LEVEL < :base_number + 1) t2) t3
     WHERE t3.i + t3.r <= ( :base_number * t3.d) + 1;
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Michael,

    From my query above, use rn instead of c2 into the main select :
    select c1, rn
    from (select decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) c1,
    myvalue-decode(mod(rownum,myvalue),0,myvalue-1,mod(rownum,myvalue)-1) c2,
    row_number() over (partition by decode(mod(rownum,myvalue),0,myvalue,mod(rownum,myvalue)) order by null) rn
    from (select 7 myvalue from dual)
    connect by level <= myvalue*myvalue)
    where c2>=rn
    order by c1
    /
    That keep you two options with the mennan's query ;-)

    HTH,

    Nicolas.
  • 443806
    443806 Member Posts: 134
    More terse, i.e. with fewer constructs?

    WITH S AS (SELECT LEVEL N FROM DUAL CONNECT BY LEVEL <= :MYMAX),
    T AS (SELECT A.N T1, B.N T2 FROM S A, S B WHERE A.N + B.N <= :MYMAX + 1),
    U AS (SELECT A.N U1, B.N U2 FROM S A, S B WHERE A.N + B.N = :MYMAX + 1)
    SELECT T.T1, U.U2 FROM T, U WHERE T.T1 = U.U1
    ORDER BY T.T1, U.U2

    T1 U2
    1 7
    1 7
    1 7
    1 7
    1 7
    1 7
    1 7
    2 6
    2 6
    2 6
    2 6
    2 6
    2 6
    3 5
    3 5
    3 5
    3 5
    3 5
    4 4
    4 4
    4 4
    4 4
    5 3
    5 3
    5 3
    6 2
    6 2
    7 1
  • ushitaki
    ushitaki Member Posts: 1,128
    edited February 2007
    select a,b from
    (
    select
    t0.rn a
    ,max(t1.rn) over() - t0.rn + 1 b
    from
    (select rownum rn from dual connect by rownum <=7) t0
    ,(select rownum rn from dual connect by rownum <=7) t1
    where t0.rn <= t1.rn
    )
    order by a,b
    ;
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited June 2007
    select a.A,max(B)
    from (select RowNum as A from all_catalog a where RowNum <= 7*(7+1)/2) a,
    (select RowNum as B,sum(RowNum) over (order by RowNum desc) as Counter
    from all_catalog
    where RowNum <=7) b
    where a.A <= b.Counter
    group by A.a
    order by A.a;
    "where RowNum <= 7*(7+1)/2" is Gauss's Algorithm to derive sum.
    http://www.uh.edu/engines/epi2087.htm
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    srinik001, ushitaki, Aketi Jyuuzou. Thanks very much for your efforts, but your queries don't give the desired output, as required in one of my later posts:

    A B
    ------
    1 1
    1 2
    1 3
    1 4
    1 5
    1 6
    1 7
    2 1
    2 2
    2 3
    2 4
    2 5
    2 6
    3 1
    3 2
    3 3
    3 4
    3 5
    4 1
    4 2
    4 3
    4 4
    5 1
    5 2
    5 3
    6 1
    6 2
    7 1

    Regards,
    Michael
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    SQL> select a.A,row_number() over(partition by max(b.B) order by a.A) as B
    2 from (select RowNum as A from all_catalog a where RowNum <= 7*(7+1)/2) a,
    3 (select RowNum as B,sum(RowNum) over (order by RowNum desc) as Counter
    4 from all_catalog
    5 where RowNum <=7) b
    6 where a.A <= b.Counter
    7 group by a.A
    8 order by a.a;

    A B
    --------- ---------
    1 1
    2 2
    3 3
    4 4
    5 5
    6 6
    7 7
    8 1
    9 2
    10 3
    11 4
    12 5
    13 6
    14 1
    15 2
    16 3
    17 4
    18 5
    19 1
    20 2
    21 3
    22 4
    23 1
    24 2
    25 3
    26 1
    27 2
    28 1
  • 121256
    121256 Member Posts: 1,054
    with t as (select level as i from dual connect by level <= 7)
    select t1.i as a, t2.i - t1.i + 1 as b from t t1, t t2 where t1.i <= t2.i order by a, b
  • 572471
    572471 Member Posts: 984 Green Ribbon
    SQL> var p number;
    SQL> exec :p:=4;
    
    PL/SQL procedure successfully completed
    p
    ---------
    4
    
    SQL> 
    SQL> select * from
      2  (select :p-trunc(dim/:p) a, s2 b from dual
      3   model
      4    dimension by (0 dim)
      5    measures(1 s, 0 s2)
      6     rules(s[for dim from 1 to power(:p,2)-1 increment 1] = mod(CV(dim),:p)+1,
      7           s2[any] = case when s[CV()]<=trunc((CV(dim))/:p)+1 then s[CV()] end))
      8   where b is not null
      9   order by a,b
     10  /
    
             A          B
    ---------- ----------
             1          1
             1          2
             1          3
             1          4
             2          1
             2          2
             2          3
             3          1
             3          2
             4          1
    
    10 rows selected
    p
    ---------
    4
    
    SQL> 
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited June 2007
    or:
    SQL> var N number
    SQL> exec :N := 7

    PL/SQL-procedure is geslaagd.

    SQL> select a
    2 , b
    3 from dual
    4 model
    5 dimension by (1 a, 1 b)
    6 measures (dummy)
    7 rules iterate (1000) until (:N - iteration_number = 0)
    8 ( dummy[iteration_number+1,for b from 1 to :N - iteration_number increment 1] = 0
    9 )
    10 /

    A B
    ----- -----
    1 1
    1 2
    1 3
    1 4
    1 5
    1 6
    1 7
    2 1
    2 2
    2 3
    2 4
    2 5
    2 6
    3 1
    3 2
    3 3
    3 4
    3 5
    4 1
    4 2
    4 3
    4 4
    5 1
    5 2
    5 3
    6 1
    6 2
    7 1

    28 rijen zijn geselecteerd.

    SQL> exec :N := 4

    PL/SQL-procedure is geslaagd.

    SQL> select a
    2 , b
    3 from dual
    4 model
    5 dimension by (1 a, 1 b)
    6 measures (dummy)
    7 rules iterate (1000) until (:N - iteration_number = 0)
    8 ( dummy[iteration_number+1,for b from 1 to :N - iteration_number increment 1] = 0
    9 )
    10 /

    A B
    ----- -----
    1 1
    1 2
    1 3
    1 4
    2 1
    2 2
    2 3
    3 1
    3 2
    4 1

    10 rijen zijn geselecteerd.
    Regards,
    Rob.

    Message was edited by:
    Rob van Wijk

    Oops, wrong output. Let me try again ...

    Message was edited by:
    Rob van Wijk

    This one looks more like it:
    SQL> var N number
    SQL> exec :N := 7

    PL/SQL-procedure is geslaagd.

    SQL> select a
    2 , b
    3 from dual
    4 model
    5 dimension by (1 a, 1 z)
    6 measures (:N b)
    7 rules iterate (1000) until (:N - iteration_number = 0)
    8 ( b[iteration_number+1,for z from 1 to :N - iteration_number increment 1] = :N - iteration_number
    9 )
    10 /

    A B
    ----- -----
    1 7
    1 7
    1 7
    1 7
    1 7
    1 7
    1 7
    2 6
    2 6
    2 6
    2 6
    2 6
    2 6
    3 5
    3 5
    3 5
    3 5
    3 5
    4 4
    4 4
    4 4
    4 4
    5 3
    5 3
    5 3
    6 2
    6 2
    7 1

    28 rijen zijn geselecteerd.

    SQL> exec :N := 4

    PL/SQL-procedure is geslaagd.

    SQL> select a
    2 , b
    3 from dual
    4 model
    5 dimension by (1 a, 1 z)
    6 measures (:N b)
    7 rules iterate (1000) until (:N - iteration_number = 0)
    8 ( b[iteration_number+1,for z from 1 to :N - iteration_number increment 1] = :N - iteration_number
    9 )
    10 /

    A B
    ----- -----
    1 4
    1 4
    1 4
    1 4
    2 3
    2 3
    2 3
    3 2
    3 2
    4 1

    10 rijen zijn geselecteerd.
This discussion has been closed.