 3,741,235 Users
 2,248,398 Discussions
 7,861,695 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
 381.2K All Categories
 2.1K Data
 204 Big Data Appliance
 1.9K Data Science
 446.6K Databases
 220.6K General Database Discussions
 3.7K Java and JavaScript in the Database
 23 Multilingual Engine
 511 MySQL Community Space
 462 NoSQL Database
 7.7K Oracle Database Express Edition (XE)
 2.8K ORDS, SODA & JSON in the Database
 448 SQLcl
 3.9K SQL Developer Data Modeler
 185.6K SQL & PL/SQL
 20.8K SQL Developer
 291.8K Development
 7 Developer Projects
 117 Programming Languages
 288.6K Development Tools
 94 DevOps
 3K QA/Testing
 645.3K Java
 18 Java Learning Subscription
 36.9K Database Connectivity
 150 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.7K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 12 Java Essentials
 141 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 197 Java User Groups
 23 JavaScript  Nashorn
 Programs
 192 LiveLabs
 34 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.7K Other Languages
 2.3K Chinese
 165 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
Pascal's triangle or binomial coefficients
146850
Member Posts: 116
<font color="red">you know Pascal's triangle like below.</font>
1
11
121
1331
14641
...
...
my question is to find out a query giving nth sequence,
that is to say,
when n=1, that query yields
1
when n=2
1
1
when n=3
1
2
1
when n=4
1
3
3
1
...
...
(oh, but you have NOT to use any subqueries)
for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
1
11
121
1331
14641
...
...
my question is to find out a query giving nth sequence,
that is to say,
when n=1, that query yields
1
when n=2
1
1
when n=3
1
2
1
when n=4
1
3
3
1
...
...
(oh, but you have NOT to use any subqueries)
for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
Comments

If you dont want subqueries, you can try in PL/SQL.
Assuming your biggest length of the charector is 6, If the length is more you can copy and paste more ElsIf's:DECLARE
Not tested !
lv_text VARCHAR2(50);
lv_len NUMBER;
CURSOR c1 IS
SELECT text, length(text)
FROM table;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO lv_text;
EXIT WHEN c1%NOTFOUND;
IF nvl(lv_length) = 1 THEN
dbms_output.put_line (lv_text);
ELSIF
nvl(lv_length) = 2 THEN
FOR i IN 1..2 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 3 THEN
FOR i IN 1..3 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 4 THEN
FOR i IN 1..4 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 5 THEN
FOR i IN 1..5 LOOP
dbms_output.put_line (substr(lv_text, i, i));
ELSIF
nvl(lv_length) = 6 THEN
FOR i IN 1..6 LOOP
dbms_output.put_line (substr(lv_text, i, i));
END IF;
END LOOP;
CLOSE c1;
END;
/
HTH
Ghulam 
Are there restrictions on creating functions?

END LOOP;FOR i IN 1..2 LOOP
dbms_output.put_line (substr(lv_text, i, i));
Sorry you have to add END LOOP; at the end of all For Loops.
HTH
Ghulam 
sorry, but i want it in one SQL , not PL/SQL
a variable n is given.
no subqueries allowed.
* this is not a sort of exam. 
and the result is n rows....
when n=1
1 (one row)
when n=2
1
1 (two rows)
where n=3
1
2
1 (three rows)
...
... 
Do you have a solution and you're just testing us, or is this a kite flying exercise to see whether its possible?* this is not a sort of exam.
I imagine it might be possible to do this with the Model clause but not having 10g to try it on I can't explore this theory at the moment.
Cheers, APC 
I recall the triangle starting at row 0 (perhaps that is a matter of choice)...
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0  Production With the Partitioning, OLAP and Data Mining options SQL> VARIABLE n NUMBER; SQL> EXEC :n := 0; PL/SQL procedure successfully completed. SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / ( 2 EXP (SUM (LN (GREATEST (LEVEL  1, 1))) 3 OVER (ORDER BY LEVEL)) * 4 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1))) 5 OVER (ORDER BY LEVEL DESC))) n 6 FROM dual 7 CONNECT BY LEVEL <= CEIL (:n + 1); N  1 SQL> EXEC :n := 1; PL/SQL procedure successfully completed. SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / ( 2 EXP (SUM (LN (GREATEST (LEVEL  1, 1))) 3 OVER (ORDER BY LEVEL)) * 4 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1))) 5 OVER (ORDER BY LEVEL DESC))) n 6 FROM dual 7 CONNECT BY LEVEL <= CEIL (:n + 1); N  1 1 SQL> EXEC :n := 2; PL/SQL procedure successfully completed. SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / ( 2 EXP (SUM (LN (GREATEST (LEVEL  1, 1))) 3 OVER (ORDER BY LEVEL)) * 4 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1))) 5 OVER (ORDER BY LEVEL DESC))) n 6 FROM dual 7 CONNECT BY LEVEL <= CEIL (:n + 1); N  1 2 1 SQL> EXEC :n := 3; PL/SQL procedure successfully completed. SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / ( 2 EXP (SUM (LN (GREATEST (LEVEL  1, 1))) 3 OVER (ORDER BY LEVEL)) * 4 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1))) 5 OVER (ORDER BY LEVEL DESC))) n 6 FROM dual 7 CONNECT BY LEVEL <= CEIL (:n + 1); N  1 3 3 1 SQL> EXEC :n := 4; PL/SQL procedure successfully completed. SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / ( 2 EXP (SUM (LN (GREATEST (LEVEL  1, 1))) 3 OVER (ORDER BY LEVEL)) * 4 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1))) 5 OVER (ORDER BY LEVEL DESC))) n 6 FROM dual 7 CONNECT BY LEVEL <= CEIL (:n + 1); N  1 4 6 4 1 SQL> EXEC :n := 5; PL/SQL procedure successfully completed. SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / ( 2 EXP (SUM (LN (GREATEST (LEVEL  1, 1))) 3 OVER (ORDER BY LEVEL)) * 4 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1))) 5 OVER (ORDER BY LEVEL DESC))) n 6 FROM dual 7 CONNECT BY LEVEL <= CEIL (:n + 1); N  1 5 10 10 5 1 6 rows selected. SQL> EXEC :n := 6; PL/SQL procedure successfully completed. SQL> SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / ( 2 EXP (SUM (LN (GREATEST (LEVEL  1, 1))) 3 OVER (ORDER BY LEVEL)) * 4 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1))) 5 OVER (ORDER BY LEVEL DESC))) n 6 FROM dual 7 CONNECT BY LEVEL <= CEIL (:n + 1); N  1 6 15 20 15 6 1 7 rows selected. SQL>

by the way, how are you going to generate multiple rows in plain SQL?
def n=5 with fac as (select rownum n,exp(sum(ln(rownum))over(order by rownum)) fac from all_objects where rownum<12) select nvl(n.fac/r.fac/nr.fac,1) from (select rownum1 r from all_objects where rownum<=&n+1), fac n, fac r, fac nr where n.n (+)=&n and r.n (+)=r and nr.n(+)=(&nr) order by r / 1 5 10 10 5 1
Message was edited by:
Laurent Schneider
padders, your one is better ;) 
note that I consider CONNECT BY without PRIOR to be disallowed... as specified in the doc, CONNECT BY must contain a PRIOR operator defining the parentchild relationship... but this is just a small note of mines and feel free to use CONNECT BY if you like / trust it ;)
Message was edited by:
Laurent Schneider
still padders method with analytics rules, because it does not use subquery 
Should this work for all values of :n?
When :n = 10...
N
0
10
45
120
210
252
210
120
45
10
0 
interesting. Padders version works for me (allowing for the fatc that the CONNECT BY trick doesn't work propperly in 9.2)...Should this work for all values of :n?
SQL> EXEC :n := 10
Cheers, APC
PL/SQL procedure successfully completed.
SQL> SELECT * FROM
2 (
3 SELECT EXP (SUM (LN (LEVEL)) OVER ()) / (:n + 1) / (
4 EXP (SUM (LN (GREATEST (LEVEL  1, 1)))
5 OVER (ORDER BY LEVEL)) *
6 EXP (SUM (LN (GREATEST (:n  (LEVEL  1), 1)))
7 OVER (ORDER BY LEVEL DESC))) n
8 FROM dual
9 CONNECT BY LEVEL <= CEIL (:n + 1))
10 /
N

1
10
45
120
210
252
210
120
45
10
1
11 rows selected.
SQL> 
There's an easy workaround for that (I'm sure you're well aware, but just for those who don't know yet):interesting. Padders version works for me (allowing for the fatc that the CONNECT BY trick doesn't work propperly in 9.2)...
SELECT EXP (SUM (LN (L)) OVER ()) / (:n + 1) / ( EXP (SUM (LN (GREATEST (L  1, 1))) OVER (ORDER BY L)) * EXP (SUM (LN (GREATEST (:n  (L  1), 1))) OVER (ORDER BY L DESC))) n FROM ( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= CEIL (:n + 1) ) /
It works perfect on my 9.2 box.
MHE 
I'm using 9.2.0.6.0... so there's a flaw with connect by in this version thats affecting the output of the query sometimes? When I use values 0 through 9 and 11 it appears to return the correct values, but for whatever reason it doesn't work when :n equals 10.
Can anyone explain how the query works, not what each function does individually, but why when you put them together they return pascal's triangle? 
Me likewiseI'm using 9.2.0.6.0basically the query is calculating the value of each element in the row; it implements the nCr method using analytic functions to generate factorials.Can anyone explain how the query works
Cheers, APC 
Thanks for your reply APC. I understand the formula (I accept that it works although I have no idea why it works). But how do the functions that padders uses perform the same functionality that a factorial (!) does?

The following are all the same. This is a (sort of) common trick for implementing factorial.
select exp(sum(ln(level))) from dual connect by level <= N exp(ln(1) + ln(2) + ... ln(N)) exp(ln(1)) * exp(ln(2)) * ... exp(ln(N)) 1*2*...N N!

select x,y,count(1) from (
select x,y,sys_connect_by_path('['x','y'>',' ') path
from (
select level x from dual
connect by level < 5
), (
select level y from dual
connect by level < 5
) connect by prior x+1 = x and prior y = y
or prior x = x and prior y+1 = y
start with x = 1 and y = 1
) group by x,y;
Nice exercise to include into a book. 
Thanks Scott, I must have missed that day in my SQL class.

good job, padders.
and my solution isSELECT EXP (SUM (LN (GREATEST (LEVEL  1, 1))) OVER ()) / EXP (SUM (LN (GREATEST (LEVEL  1, 1))) OVER (ORDER BY LEVEL)) / EXP (SUM (LN (GREATEST (:n  LEVEL, 1))) OVER (ORDER BY LEVEL DESC) ) bin_coef FROM DUAL CONNECT BY LEVEL <= :n
Query Your Dream & Future at
http://www.soqool.com 
Just a couple of variations on the theme using a table function as a row source and a userdefined product aggregate (source available on request).
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0  Production With the Partitioning, OLAP and Data Mining options SQL> VARIABLE r NUMBER; SQL> EXEC :r := 5; PL/SQL procedure successfully completed. SQL> SELECT product (GREATEST (n, 1)) OVER () / 2 product (GREATEST (n, 1)) OVER (ORDER BY n) / 3 product (GREATEST (:r  n, 1)) OVER (ORDER BY n DESC) n 4 FROM TABLE (many (0, :r)); N  1 5 10 10 5 1 6 rows selected. SQL> SELECT SUBSTR (SYS_CONNECT_BY_PATH ( 2 r, ','), 2) pascals_triangle 3 FROM (SELECT a.n an, b.n bn, 4 product (GREATEST (b.n, 1)) OVER ( 5 PARTITION BY a.n) / 6 product (GREATEST (b.n, 1)) OVER ( 7 PARTITION BY a.n ORDER BY b.n) / 8 product (GREATEST (a.n  b.n, 1)) OVER ( 9 PARTITION BY a.n ORDER BY b.n DESC) r 10 FROM TABLE (many (0, :r)) a, 11 TABLE (many (0, a.n)) b) 12 WHERE CONNECT_BY_ISLEAF = 1 13 START WITH bn = 0 14 CONNECT BY an = PRIOR an AND bn = PRIOR bn + 1; PASCALS_TRIANGLE  1 1,1 1,2,1 1,3,3,1 1,4,6,4,1 1,5,10,10,5,1 6 rows selected. SQL>

strike !

LOL. Is this better?
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0  Production With the Partitioning, OLAP and Data Mining options SQL> VARIABLE r NUMBER; SQL> EXEC :r := 10; PL/SQL procedure successfully completed. SQL> SELECT LPAD (' ', 5 * (:r  an) / 2)  2 REPLACE (SYS_CONNECT_BY_PATH ( 3 LPAD (r, 5), '/'), '/') pascals_triangle 4 FROM (SELECT a.n an, b.n bn, 5 product (GREATEST (b.n, 1)) OVER ( 6 PARTITION BY a.n) / 7 product (GREATEST (b.n, 1)) OVER ( 8 PARTITION BY a.n ORDER BY b.n) / 9 product (GREATEST (a.n  b.n, 1)) OVER ( 10 PARTITION BY a.n ORDER BY b.n DESC) r 11 FROM TABLE (many (0, :r)) a, 12 TABLE (many (0, a.n)) b) 13 WHERE CONNECT_BY_ISLEAF = 1 14 START WITH bn = 0 15 CONNECT BY an = PRIOR an AND bn = PRIOR bn + 1; PASCALS_TRIANGLE  1 1 1 1 2 1 1 3 3 1 1 4 6 4 1 1 5 10 10 5 1 1 6 15 20 15 6 1 1 7 21 35 35 21 7 1 1 8 28 56 70 56 28 8 1 1 9 36 84 126 126 84 36 9 1 1 10 45 120 210 252 210 120 45 10 1 11 rows selected. SQL>

Splendid !!
Nicolas. 
SQL> col PASCALS_TRIANGLE for a80
SQL>
SQL> with Renban as (
2 select RowNum1 as Val
3 from dual connect by Level <= 15),
4 WorkView as (
5 select Ra.Val as n,Rb.Val as r
6 from Renban Ra,Renban Rb
7 where Ra.Val >= Rb.Val),
8 RowList as (
9 select n,r,
10 case when r=0 then 1
11 else (select round(exp(sum(Ln(a.Nb.R+1)))) from WorkView b
12 where b.N=a.N
13 and b.R > 0
14 and b.R<=a.R) end as NPR,
15 case when r=0 then 1
16 else (select round(exp(sum(Ln(b.R)))) from WorkView b
17 where b.N=a.N
18 and b.R > 0
19 and b.R<=a.R) end as "R!"
20 from WorkView a),
21 PascalTriangle as (
22 select N,SubStr(sys_connect_by_path(Val,' '),2) as Pascal
23 from (select N,R,NPR / "R!" as Val from RowList)
24 where Connect_by_IsLeaf = 1
25 start with R=0
26 connect by prior N=N
27 and prior R=R1)
28 select Lpad(' ',(MaxLengthLength(Pascal))/21)  Pascal as PASCALS_TRIANGLE
29 from PascalTriangle,(select max(Length(Pascal)) as MaxLength from PascalTriangle);
PASCALS_TRIANGLE

1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1
1 6 15 20 15 6 1
1 7 21 35 35 21 7 1
1 8 28 56 70 56 28 8 1
1 9 36 84 126 126 84 36 9 1
1 10 45 120 210 252 210 120 45 10 1
1 11 55 165 330 462 462 330 165 55 11 1
1 12 66 220 495 792 924 792 495 220 66 12 1
1 13 78 286 715 1287 1716 1716 1287 715 286 78 13 1
1 14 91 364 1001 2002 3003 3432 3003 2002 1001 364 91 14 1 
Thanks, Vadim Tropashko.
I'm reading your book, SQL Design Patterns.
Query Your Dream & Future at
http://www.soqool.com
This discussion has been closed.