Loved your solution! It is a superior solution for the problem. I tried solving it before watching your solution and came up with this. If record is odd, combine text from this row and leading row. So much to improve! thanks for everything! select * from (select case when id%20 then id||' '||name||', '|| lead(id) over (order by id) || ' '|| lead(name) over (order by id) end as res from input) x where x.res IS NOT null;
Thanks for your video. I try to solve this problem first and here are my code. select id_name + ', ' + name2 as result from (select *, lead(id_name) over (order by id) as name2 from (select *, cast(id as varchar) + ' ' + name as id_name from emp_input) x) y where id % 2 0
Table name is input SELECT concat(a.id,' ',a.name,' ',b.id,' ',b.name) from input as a JOIN input as b On a.id < b.id WHERE a.id%2 = 1 and b.id%2 = 0 and b.id- a.id = 1
I haven‘t really verified my thoughts but I‘d rather go with the mod function to create buckets. In this case you won’t have to speficy how many buckets you need (you just need to think of the size of each bucket and use the row number as numerator). For this exact problem though, is your way of solving it, just perfect! Thanks for sharing 👍
Very Well Explained. MySql Solution: SELECT GROUP_CONCAT( CONCAT_WS(', ', CONCAT(id,' ',name))) as Result FROM ( SELECT id, name, ntile(20) over(order by name) as grp FROM `workers` ) as temp GROUP BY grp;
sir, make more videos on solving interview question , best teaching method and the way you explain each and every line is very much understandable. Thank you sir.
I have learned a lot from your videos! A lot of things that I thought were complex and that were actually simple to understand. Thank you so much for everything and congratulations for the channel!!
Your Videos are very helpful, gained more knowledge through your videos and I like the way you are explaining step by step and providing every dataset for practise ,Thanks a lot
with odd as ( select * from emp_input where id%20 ), even as ( select * from emp_input where id%2=0 ) select concat(odd.id," ",odd.name,",", even.id," ",even.name)as output from odd,even where even.id = odd.id+1 this is my solution
I saw one video of yours and I completely understood Sir I want to learn SQL in a proper manner can please suggest me your playlist which one I need to refer . Hope you will reply 😊
My Simple Solution with Logic :- Simply assign group number to all the records such that Emp1 and Emp2 go to group 1, Emp3 and Emp4 go to group 2 and so on. This can be simply done by dividing their serial number by 2 and then doing a ceil on it to ensure it is an integer. Now you can apply group concat based on this column. create table data( ID int, Name varchar(20) ); insert into data values (1,'Emp1'), (2,'Emp2'), (3,'Emp3'), (4,'Emp4'), (5,'Emp5'), (6,'Emp6'), (7,'Emp7'), (8,'Emp8'); select grp_no,group_concat(res_col) from( select concat(ID,' ',Name) as res_col,ceil(ID/2) as grp_no from data ) A group by grp_no;
@@techTFQ How I just ran it again and its coming as expected. You might be seeing one extra column for grp_no which can be eliminated easily by using the query inside a subquery. select output from (select grp_no, group_concat(res_col) as output from (select concat(ID, ' ', Name) as res_col, ceil(ID/2) as grp_no from data) A group by grp_no) B;
Logic is this with cte1 as (select * from emp_input where id%2=0), cte2 as (select * from emp_input where id%20) select * from cte1 a inner join cte2 b on a.id=b.id+1
You didn't use the within group order by clause in the string aggregate function. It's possible to get 2 Emp2, 1 Emp1, etc. I know because that is what I got when I left out the order clause select string_agg (concat (ID,' ', Name) , ', ' ) within group (order by ID) as output from emp_input group by round(ID/2.0, 0) /* create buckets */ What about creating one long string and then cutting it into distinct pieces...is that possible with no grouping or window clause?
I solved it using self join with cte as( select e1.id id1,e2.id id2 ,e1.name name1 ,e2.name name2 from emp_input e1 left join emp_input e2 on e1.id+1 = e2.id where e2.name is not null and e1.id % 2 0 ) select concat(id1,name1,',',id2,name2) output from cte
As an absolute beginner who started learning simple select statements, my solution was: select concat(id,name, ',' , id+1,' Emp',id+1) as Output from tbl where mod(id,2) 0; of course, this fails miserably if all names are not simply Emp and number. So, if we want to stick to very basic syntax, is it possible to concat outputs from two select statements (one with mod(id,2) 0 and the other mod(id,2) =0 in anyway? I love this site and I am sure I will be spending a lot of time here.
easy method use case statement for oracle select*from(select case when id=1 then '1 emp1,2 emp2' when id=2 then '3 emp3,4 emp4' when id=3 then '5 emp5,6 emp6' when id=4 then '7 emp7,8 emp8' end finalre from emp_input) where finalre is not null;
I have tried this..! Select Convert(varchar,X.ID)+' '+X.name+', '+ Convert(varchar,X.a) +' '+ b From ( Select ID, name, Lead(ID,1) Over(order by ID asc) a, Lead(Name,1) Over(order by Name asc) b From Input )X Where X.ID%20 Order by 1 asc
My approach using CTE, concat, MOD and join :- create table data (id int, name text); insert into data values (1,'Emp1'); insert into data values (2,'Emp2'); insert into data values (3,'Emp3'); insert into data values (4,'Emp4'); insert into data values (5,'Emp5'); insert into data values (6,'Emp6'); insert into data values (7,'Emp7'); insert into data values (8,'Emp8'); solution :- with set1 as (select *,row_number() over() as rec, concat(id,' ',name) as id_name from data where id%2 0), set2 as (select *,row_number() over() as rec, concat(id,' ',name) as id_name from data where id%2 = 0) select concat(s1.id_name,', ',s2.id_name) as Result from set1 s1 join set2 s2 on s1.rec=s2.rec; Thanks @techTFQ for such efforts 👍👍
Good evening. I am teaching myself MySQL on Workbench to apply for Data Analyst profile later on. Do you have any compilation of relevant SQL problems that I can use to practice along? Thanks.
My solution (applicable only when total number of records in input table is even) with cte as (select id, concat(id, name, ', ', lead(concat(id, name)) over (order by id)) as output from input) select output from cte where cte.id%2 = 1;
Hi Thoufiq, I came up with this solution without using any window functions or cte SELECT x.empo||', '||y.empe AS RESULT FROM (SELECT id, id||' '||name empo FROM input i1 WHERE MOD(id, 2) != 0) x , (SELECT id, id||' '||name empe FROM input i2 WHERE MOD(id, 2) = 0) y WHERE y.id - x.id = 1
we can try like this also with cte as ( select *, concat(id,+' '+ name ) as nm from emp_input) ,final as ( select *, lead (nm,1)over (order by id) as new from cte ) select concat(nm,+','+new), id from final WHERE id%2 0;
WITH CTE_1 (col_id,Name, LeadID, LeadName) as ( select col_id, Name, lead(col_id) over (order by col_id) LeadID , lead(Name) over (order by col_id) LeadName from demo2 order by col_ID ), CTE_2 (CIS) as -- since CASE CIS can not be used in where clause ( select (case when MOD(col_id,2) =1 then col_id || Name || ' , ' || LeadID || LeadName Else NULL END) as CIS from CTE_1 ) -- WHERE is processed before SELECT. It doesn't know what DerviedRegion is at that point. select * from CTE_2 where CIS IS NOT NULL
HI Taufiq , Sharing my solution %sql WITH CTE AS (select *,concat(id,' ',name) as new_val from emp_input) ,CTE1 AS (select *,concat(new_val,',',lead(new_val,1,'NothingtoADD') over(order by id)) as new_val2 from CTE) select * from CTE1 where id%2!=0
The query below works in MySQL. with a1 as ( select CONCAT(ID,'',NAME,', ') as nlist, row_number() over (order by ID) as RN from emp_input where id%2=1 ), a2 as ( select CONCAT(ID,'',NAME) as nlist, row_number() over (order by ID) as RN from emp_input where id%2=0 ) select concat(a1.nlist,a2.nlist) from a1 join a2 on a1.RN=a2.RN order by a1.RN; This also works in MySQL based on one comment under this video: SELECT CONCAT(A.id, ' ', A.name, ', ',B.id, ' ', B.name) FROM emp_input AS A INNER JOIN emp_input AS B ON A.id + 1 = B.id and A.id % 2 0;
for oracle sql select listagg(full_name,',') from (select employee_id||first_name as full_name, ntile(4) over(order by employee_id) as bucket from emps) group by bucket;
with a as (select x.id as id1,x.name as name1,y.id as id2,y.name as name2, ROW_NUMBER() over (partition by x.id order by x.id) as rn from p3_tab as x inner join p3_tab as y on (x.id%2)!=0 and x.id
select concat(a.id,' ',a.name,', ',b.id,' ',b.name) from (select * from emp_input where id%20) a join (select * from emp_input where id%2=0) b on a.id=b.id-1
This can be done without using CTE: select string_agg(name, ',') as Result from (select concat(id, ' ' , name) as name, ntile(4) over() as bucket from emp_input) as innerTable group by bucket order by bucket
Hi Toufiq, what is the difference between order by(column_name) and order by 1. along with the difference between count(column_name), count(*) and count 1. kindly do one separate video. Thanks
also please consider the below as well. Hi Toufiq, I attended sql interview and the interviewer asked me below sql query. Kindly help me with a short video so that i'll build some confidence and will crack the next interviews. Thanks SQL: Table A with column C1 has 7 records I. E 1,1,1,1,1,Null, Null and Table B with column C2 has 5 records I. E 1,1,1,2,Null. How records we will fetch by using inner join, left join, right join and full join.
with cte as (select concat(id,' ',name) as result from input) , cte2 as (select result, lead(result,1) over(order by result) end from cte) select concat(result,', ',end) as result from cte2 where result%2 != 0
select case when id%2 =1 then out else null end as result from ( select *,CONCAT(id,' ',name) +','+ lead (CONCAT(id,' ',name),1) over (order by id) as out from emp_input) a where case when id%2 =1 then out else null end is not null;
My Approach before watching the solution: with cte as (select *, concat(id ,name) as hello from productBased), cte1 as (select id,name, lag(hello) over(order by name) as hello2 ,hello from cte) select concat(hello2,",",hello) from cte1 where id%2=0;
Bro from last two days I am trying to make payment for the course... tried with four different cards ...lighthall site simply says card is declined...Is there any other way to make payment?
This is easiest solution you will ever find for this question: select concat(row1,' ',name1,',',row2,' ',name2) as result from (select e1.id as row1,e1.name as name1,e2.id as row2,e2.name as name2 from emp_input e1 join emp_input e2 on e1.id+1=e2.id where row2%2==0)
It will work with any dataset as long as the total count is in even. .. .. WITH cte1 AS ( SELECT (COUNT(*)/2)::INT AS total FROM input), cte2 AS ( SELECT (id || ' ' || name) AS result, NTILE(total) OVER(ORDER BY id ASC) AS pairs FROM input, cte1) SELECT STRING_AGG(result, ', ') AS RESULT FROM cte2 GROUP BY pairs ORDER BY 1 ASC;
select full_data from (select concat_ws(" ",id,name, lead(id,1,'empty') over(), lead(name,1,'no_name') over()) as full_data,lead(id,1,'empty') over() as num from emp_input) new_table where mod(num,2) = 0 and num not like 'empty';
Hey there, God bless your efforts. I am still new to sql with a general enquiry. How non-clustered index differs from clustered index? It has anything to do with grouping of data while indexing? Thanks a lot.
with temp as ( select id,case when mod(id,2)=1 then id+1 else id end new_id, id||' '||name as new_name from emp) select listagg(new_name,',') from temp group by new_id
select concat(B.Id -1,B.Name,",",A.Id, A.Name) from (select * FROM Employee where Mod(Id,2)=0 ) A inner join (select Id+1 as Id,Name FROM Employee where Mod(Id,2)!=0 ) B on A.Id = B.Id
My solution SELECT id || ' ' || name || ',' || id_lead || ' ' || name_lead from ( SELECT id, lead (id,1,id) over (order by id) as id_lead, name, lead (name,1,name) over (order by id) as name_lead, mod(id,2) as flag from tablename ) as X where X.flag 0 ;
Here my solution :)) select concat(x.id, x.name, ',', x.lead_id, x.lead_name) as result from ( select * , lead(id,1) over() as lead_id , lead(name,1) over() as lead_name from emp_input e1 ) x where mod(x.id, 2) 0;
We Hire dedicated teacher. Here is a jsp page and on the other hand asp page now here is a store procedure that need to be invoked. What purpose it will solve this is none of your business. Time will explain you.
with cte as ( SELECT id, name, CASE WHEN id %2 0 THEN concat(id,' ',name,',',' ',lead(id,1) over (order by id),' ',lead(name,1) over (order by id)) ELSE 'None' END AS output from emp_input ) select output from cte where output 'None';
MySQL Solution (For Freshers) With CTE as (Select *,concat(id," ",Name) as N1 from emp_input), CTE2 as (Select *,Lead(N1,1) Over (Order by id) as N2,row_number() over (Order By id) as RN from CTE) Select concat(N1,",",N2) as Result from CTE2 Where N2 is not null and Rn%2!=0;
My Code for that problem with combines as ( select id, name, concat(id,' ',name) as concats from emp_input ) , rnking as ( SELECT id, name, concats, row_number()over(order by concats) as rnks from combines ), new_rnking as ( SELECT id, name, concats, rnks, case when rnks%2=0 then rnks - 1 else rnks end as new_rnks from rnking ) SELECT GROUP_concat(concats separator ',') as Result from new_rnking GROUP by new_rnks
WITH t1 as(select *, id||' '||employee as joined FROM sample), t2 as (SELECT id, joined, LEAD(joined,1) over () as result from t1) select joined||', '||result from t2 WHERE (id*1.0)%2 != 0
WITH cte1 AS (SELECT *, CONCAT(id,name) as mer_name, CASE WHEN id%2=0 THEN id ELSE LEAD(id) OVER(ORDER BY id) END ids FROM emp_input) SELECT GROUP_CONCAT(mer_name) as results FROM cte1 GROUP BY ids;
HI, here is my version of solution for MYSQL with cte1 as (SELECT concat(id," ",name) as result from emp_input), CTE2 as (SELECT result, LEAD (result) over() as result2 FROM cte1) SELECT concat(result, "," " ",result2) as final_result FROM cte2 where mod(concat(result, "," " ",result2),2)=1
Hi Toufiq, Please check this solution : SELECT CONCAT(A.id, ' ', A.name, ', ', B.id, ' ', B.name) AS Req_Result FROM emp_input AS A INNER JOIN emp_input AS B ON A.id + 1 = B.id WHERE (A.id % 2 0) ;
Loved your solution! It is a superior solution for the problem. I tried solving it before watching your solution and came up with this. If record is odd, combine text from this row and leading row. So much to improve! thanks for everything!
select * from
(select case when id%20 then
id||' '||name||', '||
lead(id) over (order by id) || ' '||
lead(name) over (order by id)
end as res
from input) x
where x.res IS NOT null;
Thanks for your video. I try to solve this problem first and here are my code.
select id_name + ', ' + name2 as result
from (select *,
lead(id_name) over (order by id) as name2
from (select *,
cast(id as varchar) + ' ' + name as id_name
from emp_input) x) y
where id % 2 0
Table name is input
SELECT concat(a.id,' ',a.name,' ',b.id,' ',b.name) from input as a
JOIN input as b
On a.id < b.id
WHERE a.id%2 = 1 and b.id%2 = 0 and b.id- a.id = 1
Applicable, concise, and practical. Thank you 👏🏽
I learn and learned all tricky SQL part from your videos only..request you to make video on "sql Index" with practical examples
thank you and noted. I'll get back to making tutorial videos after couple of months
@@techTFQ thanks a lot..will wait..
@@techTFQbro sql install error in my pc
I haven‘t really verified my thoughts but I‘d rather go with the mod function to create buckets. In this case you won’t have to speficy how many buckets you need (you just need to think of the size of each bucket and use the row number as numerator). For this exact problem though, is your way of solving it, just perfect! Thanks for sharing 👍
Very Well Explained.
MySql Solution:
SELECT GROUP_CONCAT( CONCAT_WS(', ', CONCAT(id,' ',name))) as Result FROM
(
SELECT id, name, ntile(20) over(order by name) as grp
FROM `workers`
) as temp
GROUP BY grp;
Great explanation, got to learn about the ntile function today, thanks a lot sir!
sir, make more videos on solving interview question , best teaching method and the way you explain each and every line is very much understandable. Thank you sir.
I have learned a lot from your videos! A lot of things that I thought were complex and that were actually simple to understand. Thank you so much for everything and congratulations for the channel!!
Your Videos are very helpful, gained more knowledge through your videos and I like the way you are explaining step by step and providing every dataset for practise ,Thanks a lot
You're welcome
with odd as (
select * from emp_input where id%20
),
even as (
select * from emp_input where id%2=0
)
select concat(odd.id," ",odd.name,",", even.id," ",even.name)as output from odd,even where even.id = odd.id+1
this is my solution
Simply one of the best channels!!!
I saw one video of yours and I completely understood Sir I want to learn SQL in a proper manner can please suggest me your playlist which one I need to refer . Hope you will reply 😊
I started watching your videos , very excellent explanation . will wait for more such videos.
your videos are the most helpful ones
Please upload every week solve SQL projects, also could you please start a tutorial series for SQL 0 to 360 Degree❤
Good explaination. Thank you
thanks a lot sir. it would be great if you post list of interview based sal questions and solutions as you did before. it really helped me
Awsome that was easyily done...thanks brother...
Extremely good instruction, bro
Glad you think so!
My Simple Solution with Logic :-
Simply assign group number to all the records such that Emp1 and Emp2 go to group 1, Emp3 and Emp4 go to group 2 and so on.
This can be simply done by dividing their serial number by 2 and then doing a ceil on it to ensure it is an integer.
Now you can apply group concat based on this column.
create table data(
ID int,
Name varchar(20)
);
insert into data values
(1,'Emp1'),
(2,'Emp2'),
(3,'Emp3'),
(4,'Emp4'),
(5,'Emp5'),
(6,'Emp6'),
(7,'Emp7'),
(8,'Emp8');
select grp_no,group_concat(res_col)
from(
select concat(ID,' ',Name) as res_col,ceil(ID/2) as grp_no
from data
) A
group by grp_no;
i just executed your solution and the output is incorrect
@@techTFQ How I just ran it again and its coming as expected. You might be seeing one extra column for grp_no which can be eliminated easily by using the query inside a subquery.
select output
from
(select grp_no,
group_concat(res_col) as output
from
(select concat(ID, ' ', Name) as res_col,
ceil(ID/2) as grp_no
from data) A
group by grp_no) B;
Liked your solution only ;-)
Thank you brother 🙏🏼
Logic is this
with cte1 as (select * from emp_input
where id%2=0),
cte2 as (select * from emp_input
where id%20)
select * from cte1 a
inner join cte2 b on a.id=b.id+1
Excellent explanation bro.... 👍👍👍 Loved it
Thanks for these kind of videos
You didn't use the within group order by clause in the string aggregate function. It's possible to get 2 Emp2, 1 Emp1, etc. I know because that is what I got when I left out the order clause
select string_agg (concat (ID,' ', Name) , ', ' ) within group (order by ID) as output
from emp_input
group by round(ID/2.0, 0) /* create buckets */
What about creating one long string and then cutting it into distinct pieces...is that possible with no grouping or window clause?
Nice Solution Brother
Can't we do it using case statements and then using concat and max ?
Please Suggest
there can be multiple diff soln for this. may be you try and share solution
@@techTFQ how about this?
select
case id % 2 != 0:
concat(
concat(id, name),
concat(lead(id), lead(name)
)
from INPUT
That’s grt bro , 🎉
I solved it using self join
with cte as(
select e1.id id1,e2.id id2 ,e1.name name1 ,e2.name name2 from emp_input e1 left join emp_input e2
on e1.id+1 = e2.id
where e2.name is not null and e1.id % 2 0 )
select concat(id1,name1,',',id2,name2) output from cte
As an absolute beginner who started learning simple select statements, my solution was:
select concat(id,name, ',' , id+1,' Emp',id+1) as Output from tbl
where mod(id,2) 0;
of course, this fails miserably if all names are not simply Emp and number. So, if we want to stick to very basic syntax, is it possible to concat outputs from two select statements (one with mod(id,2) 0 and the other mod(id,2) =0 in anyway?
I love this site and I am sure I will be spending a lot of time here.
Nice explanation bro 👍 👌 👏
Thank you so much 🙂
You are amazing bhai, thank you
Thank you so much Ahmed 😀
You are awesome!
easy method use case statement for oracle
select*from(select case when id=1 then '1 emp1,2 emp2'
when id=2 then '3 emp3,4 emp4'
when id=3 then '5 emp5,6 emp6'
when id=4 then '7 emp7,8 emp8'
end finalre from emp_input) where finalre is not null;
Thanks a lot, Great detailed video. Please make video detail video on Match_recognize() function.
I have tried this..!
Select Convert(varchar,X.ID)+' '+X.name+', '+ Convert(varchar,X.a) +' '+ b
From ( Select ID, name, Lead(ID,1) Over(order by ID asc) a, Lead(Name,1) Over(order by Name asc) b
From Input )X
Where X.ID%20
Order by 1 asc
Awesome
My approach using CTE, concat, MOD and join :-
create table data (id int, name text);
insert into data values (1,'Emp1');
insert into data values (2,'Emp2');
insert into data values (3,'Emp3');
insert into data values (4,'Emp4');
insert into data values (5,'Emp5');
insert into data values (6,'Emp6');
insert into data values (7,'Emp7');
insert into data values (8,'Emp8');
solution :-
with
set1 as (select *,row_number() over() as rec, concat(id,' ',name) as id_name from data where id%2 0),
set2 as (select *,row_number() over() as rec, concat(id,' ',name) as id_name from data where id%2 = 0)
select concat(s1.id_name,', ',s2.id_name) as Result from set1 s1 join set2 s2 on s1.rec=s2.rec;
Thanks @techTFQ for such efforts 👍👍
Here, if I am unsure on the numeric expression to be given in NTILE() can I do something like COUNT(id)/2=4, in order to create dynamic?
Awesome explanation ❤️
Good evening. I am teaching myself MySQL on Workbench to apply for Data Analyst profile later on. Do you have any compilation of relevant SQL problems that I can use to practice along? Thanks.
Thank you so much Bro!!
You're welcome!
My solution (applicable only when total number of records in input table is even)
with cte as (select id, concat(id, name, ', ', lead(concat(id, name)) over (order by id)) as output from input)
select output from cte where cte.id%2 = 1;
Hi Thoufiq,
I came up with this solution without using any window functions or cte
SELECT x.empo||', '||y.empe AS RESULT
FROM (SELECT id, id||' '||name empo
FROM input i1
WHERE MOD(id, 2) != 0) x
,
(SELECT id, id||' '||name empe
FROM input i2
WHERE MOD(id, 2) = 0) y
WHERE y.id - x.id = 1
Please make videos on hive queries bigdata
noted bro
Explaing phase is very fast, some may not catch up your speed, otherwise your explanation is very good.
we can try like this also
with cte as (
select *,
concat(id,+' '+ name ) as nm
from emp_input) ,final as (
select *,
lead (nm,1)over (order by id) as new
from cte )
select concat(nm,+','+new), id
from final WHERE id%2 0;
select string_agg(Concat(id, ' ', name), ', ')
from emp_input
group by id - case when id % 2 = 0 then 1 else 0 end;
AWSOMEEEEEEEEE
Thanks
your welcome
WITH CTE_1 (col_id,Name, LeadID, LeadName) as
(
select col_id, Name, lead(col_id) over (order by col_id) LeadID , lead(Name) over (order by col_id) LeadName
from demo2 order by col_ID
),
CTE_2 (CIS) as -- since CASE CIS can not be used in where clause
(
select (case when MOD(col_id,2) =1 then col_id || Name || ' , ' || LeadID || LeadName Else NULL END) as CIS from CTE_1
) -- WHERE is processed before SELECT. It doesn't know what DerviedRegion is at that point.
select * from CTE_2 where CIS IS NOT NULL
Thanks bro
HI Taufiq ,
Sharing my solution
%sql WITH CTE AS (select *,concat(id,' ',name) as new_val from emp_input)
,CTE1 AS (select *,concat(new_val,',',lead(new_val,1,'NothingtoADD') over(order by id)) as new_val2 from CTE)
select * from CTE1 where id%2!=0
Can you make videos on product metrics
Sir,
Trigger, cursor aur user defined function par video banayiye
please make video on USER DEFINED FUNCTIONS in sql
string_ agg it can be used in oracle
All I did differently was use ceil(id/2) as buckets
nice 👍
The query below works in MySQL.
with a1 as (
select CONCAT(ID,'',NAME,', ') as nlist,
row_number() over (order by ID) as RN
from emp_input
where id%2=1
),
a2 as (
select CONCAT(ID,'',NAME) as nlist,
row_number() over (order by ID) as RN
from emp_input
where id%2=0
)
select concat(a1.nlist,a2.nlist)
from a1
join a2
on a1.RN=a2.RN
order by a1.RN;
This also works in MySQL based on one comment under this video:
SELECT CONCAT(A.id, ' ', A.name, ', ',B.id, ' ', B.name)
FROM emp_input AS A INNER JOIN
emp_input AS B ON A.id + 1 = B.id and A.id % 2 0;
for oracle sql
select listagg(full_name,',') from (select employee_id||first_name as full_name, ntile(4) over(order by employee_id) as bucket from emps)
group by bucket;
with a as (select x.id as id1,x.name as name1,y.id as id2,y.name as name2,
ROW_NUMBER() over (partition by x.id order by x.id) as rn
from p3_tab as x
inner join p3_tab as y
on (x.id%2)!=0 and x.id
select result
from
(select id, (concat(id, name, lead(id), lead(name))) result
from input)
where id%2 0
👌🏻👌🏻👌🏻👌🏻👌🏻👌🏻
Thank you :)
with cte as
(select *,
ntile(4) over() result1
from input)
select group_concat(id," ",name) Result from cte
group by result1
Make a video on views
It is already there
th-cam.com/video/cLSxasHg9WY/w-d-xo.html
already did. check in my channel
what if you do not know the number of rows are 8, how would you generalize for any number of rows?
select concat(a.id,' ',a.name,', ',b.id,' ',b.name)
from
(select * from emp_input where id%20) a
join
(select * from emp_input where id%2=0) b
on a.id=b.id-1
This can be done without using CTE:
select string_agg(name, ',') as Result
from (select
concat(id, ' ' , name) as name,
ntile(4) over() as bucket
from emp_input) as innerTable
group by bucket
order by bucket
A CTE and a derived table are essentially the same here and will produce an identical execution plan!
Hi Toufiq,
what is the difference between order by(column_name) and order by 1. along with the difference between count(column_name), count(*) and count 1. kindly do one separate video. Thanks
i have explained this a few times in different videos but may be will consider your suggestion for a future video
@@techTFQ thank you..!! Toufiq. for your quick response
also please consider the below as well.
Hi Toufiq,
I attended sql interview and the interviewer asked me below sql query. Kindly help me with a short video so that i'll build some confidence and will crack the next interviews. Thanks
SQL:
Table A with column C1 has 7 records I. E
1,1,1,1,1,Null, Null
and
Table B with column C2 has 5 records I. E
1,1,1,2,Null. How records we will fetch by using inner join, left join, right join and full join.
@@rameshthanikonda5089 is it pwc question?
@@ankushsharma3965 I don't remember at the moment. If you know the answer please comment here. Thanks
with cte as
(select concat(id,' ',name) as result
from input) ,
cte2 as
(select result,
lead(result,1) over(order by result) end
from cte)
select concat(result,', ',end) as result from cte2
where result%2 != 0
Hi, when is your next class?
Sir I have learned Oracle SQL, so my question is that learning PL/SQL will give me a added advantage or it's not that necessary
depends on your job. some jobs like sql developers will need PL/SQL but a data analyst role will not need it
@@techTFQ
Thank you sir...
@@techTFQ
Sir can you please tell me how to practice SQL question for Analyst role...
Check out StrataScratch and LeetCode
@@techTFQ
Sure sir
Sir is that free or paid platform.. Just had last query.
select case when id%2 =1 then out else null end as result
from (
select *,CONCAT(id,' ',name) +','+ lead (CONCAT(id,' ',name),1) over (order by id) as out
from emp_input) a
where case when id%2 =1 then out else null end is not null;
Done
I think we can do this by concat and self join also
i think we can do this in 10-15 different ways. not only this problem most of sql problems can be solved in multiple different ways
My Approach before watching the solution:
with cte as (select *, concat(id ,name) as hello from productBased),
cte1 as (select id,name, lag(hello) over(order by name) as hello2 ,hello from cte)
select concat(hello2,",",hello) from cte1
where id%2=0;
String _agg function is not working in Oracle, it is showing invalid identifier.
Why???
Bro from last two days I am trying to make payment for the course... tried with four different cards ...lighthall site simply says card is declined...Is there any other way to make payment?
Hi help me on this how to create unique id but I'd contains date-number, numbers is reset when the date is changed. Number start from 1
This is easiest solution you will ever find for this question:
select concat(row1,' ',name1,',',row2,' ',name2) as result
from (select e1.id as row1,e1.name as name1,e2.id as row2,e2.name as name2
from emp_input e1
join emp_input e2
on e1.id+1=e2.id
where row2%2==0)
It will work with any dataset as long as the total count is in even.
..
..
WITH cte1 AS (
SELECT (COUNT(*)/2)::INT AS total
FROM input),
cte2 AS (
SELECT (id || ' ' || name) AS result, NTILE(total) OVER(ORDER BY id ASC) AS pairs
FROM input, cte1)
SELECT STRING_AGG(result, ', ') AS RESULT
FROM cte2
GROUP BY pairs
ORDER BY 1 ASC;
select full_data from (select concat_ws(" ",id,name,
lead(id,1,'empty') over(),
lead(name,1,'no_name') over()) as full_data,lead(id,1,'empty') over() as num from emp_input)
new_table where mod(num,2) = 0 and num not like 'empty';
Input
id product
1 Choc
2 cadb
3 dairy
4 Choc
5 ecli
6 Choc
7 milky
Output
id product Sta
1 Choc 1
2 cadb 2
3 dairy 3
4 Choc 1
5 ecli 2
6 Choc 1
7 milky 2
when product choc encountered irteration should reset , can you help to provide a saolution
Hey there,
God bless your efforts.
I am still new to sql with a general enquiry.
How non-clustered index differs from clustered index?
It has anything to do with grouping of data while indexing?
Thanks a lot.
Pls show answers in my sql too
with temp as (
select id,case when mod(id,2)=1 then id+1 else id end new_id, id||' '||name as new_name from emp)
select listagg(new_name,',') from temp group by new_id
select concat(B.Id -1,B.Name,",",A.Id, A.Name) from
(select * FROM Employee where Mod(Id,2)=0 ) A
inner join (select Id+1 as Id,Name FROM Employee where Mod(Id,2)!=0 ) B
on A.Id = B.Id
My solution
SELECT id || ' ' || name || ',' || id_lead || ' ' || name_lead
from
(
SELECT
id,
lead (id,1,id) over (order by id) as id_lead,
name,
lead (name,1,name) over (order by id) as name_lead,
mod(id,2) as flag
from tablename
) as X
where X.flag 0 ;
Here my solution :))
select concat(x.id, x.name, ',', x.lead_id, x.lead_name) as result
from (
select *
, lead(id,1) over() as lead_id
, lead(name,1) over() as lead_name
from emp_input e1
) x
where mod(x.id, 2) 0;
We Hire dedicated teacher. Here is a jsp page and on the other hand asp page now here is a store procedure that need to be invoked. What purpose it will solve this is none of your business. Time will explain you.
with cte as
(
SELECT id, name,
CASE
WHEN id %2 0 THEN concat(id,' ',name,',',' ',lead(id,1) over (order by id),' ',lead(name,1) over (order by id))
ELSE 'None'
END AS output
from emp_input
)
select output from cte where output 'None';
MySQL Solution (For Freshers)
With CTE as
(Select *,concat(id," ",Name) as N1 from emp_input),
CTE2 as
(Select *,Lead(N1,1) Over (Order by id) as N2,row_number() over (Order By id) as RN from CTE)
Select concat(N1,",",N2) as Result from CTE2 Where N2 is not null and Rn%2!=0;
My Code for that problem with combines as (
select
id,
name,
concat(id,' ',name) as concats
from emp_input
) , rnking as (
SELECT
id,
name,
concats,
row_number()over(order by concats) as rnks
from combines
), new_rnking as (
SELECT
id,
name,
concats,
rnks,
case when rnks%2=0 then rnks - 1 else rnks end as new_rnks
from rnking
)
SELECT
GROUP_concat(concats separator ',') as Result
from new_rnking
GROUP by new_rnks
WITH t1 as(select *,
id||' '||employee as joined
FROM sample),
t2 as (SELECT id, joined,
LEAD(joined,1) over () as result
from t1)
select joined||', '||result
from t2
WHERE (id*1.0)%2 != 0
SELECT id || name || ‘,’ ,
LEAD(id) OVER(), LEAD(name) OVER()
FROM table_name
WHERE MOD(id,2) 1
sir u are indian i think
WITH cte1 AS (SELECT *,
CONCAT(id,name) as mer_name,
CASE WHEN id%2=0 THEN id
ELSE LEAD(id) OVER(ORDER BY id)
END ids
FROM emp_input)
SELECT GROUP_CONCAT(mer_name) as results
FROM cte1
GROUP BY ids;
HI, here is my version of solution for MYSQL
with cte1 as
(SELECT concat(id," ",name) as result
from emp_input),
CTE2 as
(SELECT result,
LEAD (result) over() as result2
FROM cte1)
SELECT concat(result, "," " ",result2) as final_result
FROM cte2
where mod(concat(result, "," " ",result2),2)=1
Hi Toufiq,
Please check this solution :
SELECT CONCAT(A.id, ' ', A.name, ', ', B.id, ' ', B.name) AS Req_Result
FROM emp_input AS A INNER JOIN
emp_input AS B ON A.id + 1 = B.id
WHERE (A.id % 2 0)
;
The solution is pretty good, One possible optimization is to put that condition in the JOIN clause only instead of in the WHERE clause