Without using a recursive CTE, I was unsure how to approach this, but after watching your video, I found it really insightful. The way you explain things is excellent. Thank you!
Thanks for bringing this on the video.. here's my attempt on SQL server: ===================================== with series as (select * from generate_series(1, (select MAX(num) from #numbers), 1)) select s1.value from series s1 cross join series s2 where s2.value
Really like the problem statement it was seems like easy at first but upon watching the video realised that simple cross join will not work. However, when recursive cte is not allowed than we should not use approach of create table using r_cte. So Last solution orr approach is the only approach I guess we can follow.
Hello Ankit, this is my solution: with cross_data as ( select a.int_numbers as seq_numbers, b.int_numbers from_b, rank() over(partition by a.int_numbers order by b.int_numbers) as rnk from tbl_numbers a cross join tbl_numbers b ) select seq_numbers from cross_data where rnk
Hi @aman_mashetty5185, I want to learn Python for DA can you include me as well while practicing, please. It would be more helpful for me to get started
@@saikanth447 firstly get basic understanding of Pandas, numpy and seaborn,matplotlib packages then use stratascratch or leet code for solving easy question and watch youtube videos its all about how would you practice thats all..
i have tried this solution using connect by clause with new_id as ( select level as id from dual connect by level =k.id and t.id in (select id from case2) order by t.id , k.id ; if we skip any value this wil give correct result
Mysql Solution: with recursive cte as ( select max(n) as n from numbers union all select n - 1 from cte where n - 1 >= 1 ) select n2.n from numbers as n1 cross join numbers as n2 on n1.n
Hi Ankit, great solution ...i want to know how 'with recursive cte' and 'with cte' differ from each other. In this case, should'nt we be using recursive cte.....
Hi Ankit , I have had this doubt for many years. What is equi join ? Can inner and outer joins be equi joins ? I appreciate your reply and the clarity you will provide. Thank you
with recursive no_of_times_no as( select n,1 as num from numbers union all select nn.n, non.num + 1 as num from no_of_times_no non inner join numbers nn on non.n = nn.n and non.num< nn.n ) select n from no_of_times_no order by n;
WITH numbers as ( SELECT top 100 rownumber() over (order by (select null)) as num FROM sys.objects ) select i.value FROM input_table I JOIN numbers n on n.num
-- Sol 1 ;with cte as ( select *,1 as n1 from #numbers ) select n from cte cross apply generate_series (n1,n) order by n -- Sol 2 ; with cte as ( select min(n) as min_n , max(n) as max_n from #numbers ) , cte2 as ( select value from cte cross apply generate_series (min_n,max_n) ) select n from cte2 c left join #numbers n on c.value n_counter ) select n from r_cte order by n
Ankit hi. Thanks a lot. But I think your explanation of recursive CTE is little bit wrong. 1. In the first step we get all rows from numbers, it is 1 to 5. 2. in the second step we get rows from CTE according WHERE statement, it is 2 to 5. And the important thing is that the step 3 is going to contain rows we just got in step 2. 3. in the third step we again get rows from CTE according WHERE statement, it is 3 to 5. And so on to step 5 It means in any follow step our table will contain rows from the only previous CTE. Of course, if I understand Recursive CTE correctly 🙂
Maybe I didn't explain correctly. What I was trying to say is for the anchor element all 5 rows will go then for each row the next iteration will take place and then so on until the where condition is meeting for each row individually.
Hi ankit is this approach was correct without cte SELECT n FROM numbers, (SELECT 1 AS rep UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS r WHERE r.rep
@@subhashyadav9262 I mentioned in the solution that 1st query is for continuous values starting from 1 and recursive solution will work everywhere. I didn't go through the video, I saw the question then wrote the query and then posted it .
@@ankitbansal6 I do not understand the usage of hybrid solution if we are using recursive cte then we will go with the recursive cte solution why will we use hybrid. One thing will be common in every solution we need to have continuous number series starting from 1 to the maximum number of the table to achieve this solution.
Without using a recursive CTE, I was unsure how to approach this, but after watching your video, I found it really insightful. The way you explain things is excellent. Thank you!
second approach using self/cross join is very cool!
Thanks for bringing this on the video..
here's my attempt on SQL server:
=====================================
with series as (select * from generate_series(1, (select MAX(num) from #numbers), 1))
select s1.value
from series s1
cross join series s2
where s2.value
Really like the problem statement it was seems like easy at first but upon watching the video realised that simple cross join will not work.
However, when recursive cte is not allowed than we should not use approach of create table using r_cte. So Last solution orr approach is the only approach I guess we can follow.
Hello Ankit, this is my solution:
with cross_data as (
select
a.int_numbers as seq_numbers, b.int_numbers from_b,
rank() over(partition by a.int_numbers order by b.int_numbers) as rnk
from tbl_numbers a
cross join tbl_numbers b
)
select seq_numbers from
cross_data where rnk
Superb explanation Ankit 👌 👏 👍
This should work:
select n
from numbers
cross apply generate_series(1, n)
Thank you please can you upload more videos on python your explanation is incredible
Awesome
superb explanation...! in python df = pd.DataFrame({'num': [1, 2, 3, 4, 5,9]})
df_repeated = df.loc[df.index.repeat(df['num'])].reset_index(drop=True)
df_repeated
Hi @aman_mashetty5185, I want to learn Python for DA can you include me as well while practicing, please. It would be more helpful for me to get started
@@saikanth447 firstly get basic understanding of Pandas, numpy and seaborn,matplotlib packages then use stratascratch or leet code for solving easy question and watch youtube videos its all about how would you practice thats all..
i have tried this solution using connect by clause
with new_id as
(
select level as id from dual
connect by level =k.id and t.id in (select id from case2) order by t.id , k.id ;
if we skip any value this wil give correct result
Mysql Solution: with recursive cte as (
select max(n) as n from numbers
union all
select n - 1 from cte
where n - 1 >= 1
)
select n2.n from numbers as n1
cross join numbers as n2 on n1.n
mast qsn tha ye
Hi Ankit
Here is my solution
Select n
From
(Select unnest(string_to_array(repeat(concat(n:: char,' '),n),' '))
as n
From numbers) a
Where n ''
select n2.n from numbers n1
join numbers n2 on n1.n
If input is 1,5 then it won't work
We can also do it using Nestled CTEs instead of recursive ones! Am I right?
sitr how i can learn recursive cte,that the only thing in sql which find me as difficult now
Hi Ankit, great solution ...i want to know how 'with recursive cte' and 'with cte' differ from each other. In this case, should'nt we be using recursive cte.....
In SQL server you don't have to add recursive keyword. Otherwise it's the same.
Hi Ankit , I have had this doubt for many years. What is equi join ? Can inner and outer joins be equi joins ? I appreciate your reply and the clarity you will provide. Thank you
It's the same thing. Equi join means join based on equal to condition
@@ankitbansal6 yes ,but can joins like left and right joins act as equi joins if join condition uses = operator ?
select b.a from xy a cross join xy b
where a.a
If input is 1,5 it won't work
with recursive no_of_times_no as(
select n,1 as num from numbers
union all
select nn.n, non.num + 1 as num from no_of_times_no non inner join numbers nn
on non.n = nn.n and non.num< nn.n
)
select n from no_of_times_no order by n;
Happy Teacher's Day.
Thank you! 😃
WITH numbers as (
SELECT top 100 rownumber() over (order by (select null)) as num
FROM sys.objects
)
select i.value
FROM input_table I
JOIN numbers n on n.num
-- Sol 1
;with cte as (
select *,1 as n1 from #numbers
)
select n from cte cross apply
generate_series (n1,n)
order by n
-- Sol 2
; with cte as (
select min(n) as min_n , max(n) as max_n from #numbers
)
, cte2 as (
select value from cte cross apply
generate_series (min_n,max_n)
)
select n from cte2 c
left join #numbers n
on c.value n_counter
)
select n from r_cte order by n
Ankit hi. Thanks a lot. But I think your explanation of recursive CTE is little bit wrong.
1. In the first step we get all rows from numbers, it is 1 to 5.
2. in the second step we get rows from CTE according WHERE statement, it is 2 to 5.
And the important thing is that the step 3 is going to contain rows we just got in step 2.
3. in the third step we again get rows from CTE according WHERE statement, it is 3 to 5.
And so on to step 5
It means in any follow step our table will contain rows from the only previous CTE.
Of course, if I understand Recursive CTE correctly 🙂
Maybe I didn't explain correctly. What I was trying to say is for the anchor element all 5 rows will go then for each row the next iteration will take place and then so on until the where condition is meeting for each row individually.
Hi ankit is this approach was correct without cte
SELECT n
FROM numbers,
(SELECT 1 AS rep UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5) AS r
WHERE r.rep
with recursive cte as(
select min(num) as n from numbers
union all
select n+1
from cte
where n=c1.n
group by n1.num
order by n1.num;
my approach
solution for continuous values:
select n2.* from numbers n1 inner join numbers n2 on n1.n
If input is 1,5 it will not work
@@ankitbansal6 Agree With you
@@subhashyadav9262 I mentioned in the solution that 1st query is for continuous values starting from 1 and recursive solution will work everywhere. I didn't go through the video, I saw the question then wrote the query and then posted it .
@@ankitbansal6 I do not understand the usage of hybrid solution if we are using recursive cte then we will go with the recursive cte solution why will we use hybrid. One thing will be common in every solution we need to have continuous number series starting from 1 to the maximum number of the table to achieve this solution.
SELECT y.A as ya FROM counting1 x
JOIN counting1 y
ON x.A