Please follow us / dev.19.community OVER ([PARTITION BY columns] [ORDER BY columns]) learnsql.com/blog/sql-over-cl... Thanks for watching us ... Dev19🖤
SELECT MAX(IF(OCCUPATION = 'DOCTOR',NAME, NULL)) AS DOCTOR, min(IF(OCCUPATION ='PROFESSOR',NAME, NULL)) AS PROFESSOR, MIN(IF(OCCUPATION = 'SINGER',NAME, NULL)) AS SINGER, max(IF(OCCUPATION = 'ACTOR',NAME, NULL)) AS ACTOR FROM (SELECT NAME, OCCUPATION, Row_number() Over(PARTITION BY occupation ORDER BY name)as row_num FROM occupations) as ord group by row_num
Your video saves my day, I spent 5hrs working on this one in my university and your video explains exacltly in the way I understood it. Btw, I want to ask for the MIN and MAX function in this case, it is just there because we are using the groupby function and it doesn't have any effect, right? ❤❤
CREATE VIEW repeatedQ AS select name, occupation, row_number() over (PARTITION BY occupation order by name) as rowNo from occupations; select (select name from repeatedQ as r2 where occupation = 'Doctor' and r2.rowNo = r1.rowNo) as Doctor, (select name from repeatedQ as r2 where occupation = 'Professor' and r2.rowNo = r1.rowNo) as Professor, (select name from repeatedQ as r2 where occupation = 'Singer' and r2.rowNo = r1.rowNo) as Singer, (select name from repeatedQ as r2 where occupation = 'Actor' and r2.rowNo = r1.rowNo) as Actor from repeatedQ as r1 group by rowNo;
SELECT [Doctor], [Professor], [Singer], [Actor] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY NAME ASC) ROW_NUM, Name, Occupation FROM OCCUPATIONS ) AS SOURCE_TABLE PIVOT ( MIN(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor]) ) AS PIVOT_TABLE
very nice explanation bro
Of all the videos out there about this problem, you are the one who explained it clearly. Great job. Do more such videos.
This is well explained out of all the videos available on TH-cam for this particular problem, Thanks
literally, u r the only one who has explained ...rest of them have just read the code as it is ..well done
So well explained!!!!!!!Thank You
wonderful explanation ! well understood especially how min and max function works in here thx!!!
Great to hear!
Why max function is not returning the maximum row_number ?
wonderfull
Why are we using the aggregate function Min() for each of the if condition for occupations?
to use group by we have to make it compulsory to use an agrregate function while selecting
Good work broo....keep it up.....try to use language in which you are more comfortable....just a suggestion
Very helpful
Thankyou bro it helped me
thank you very much.....❤
thank you so much
thankyou sooo much
SELECT
MAX(IF(OCCUPATION = 'DOCTOR',NAME, NULL)) AS DOCTOR,
min(IF(OCCUPATION ='PROFESSOR',NAME, NULL)) AS PROFESSOR,
MIN(IF(OCCUPATION = 'SINGER',NAME, NULL)) AS SINGER,
max(IF(OCCUPATION = 'ACTOR',NAME, NULL)) AS ACTOR
FROM
(SELECT NAME, OCCUPATION, Row_number() Over(PARTITION BY occupation ORDER BY name)as row_num FROM occupations) as ord group by row_num
as ord (before group by) last line mein kyun ?
@@Stayin_delulu_is_the_soluluu_ to declare as a temp TABLE with name ord, which we used further
Your video saves my day, I spent 5hrs working on this one in my university and your video explains exacltly in the way I understood it. Btw, I want to ask for the MIN and MAX function in this case, it is just there because we are using the groupby function and it doesn't have any effect, right? ❤❤
Without GroupBy also we can use min max , no issue
❤❤❤❤❤❤❤❤❤❤❤❤❤❤
CREATE VIEW repeatedQ AS
select name, occupation, row_number() over (PARTITION BY occupation order by name) as rowNo
from occupations;
select
(select name from repeatedQ as r2 where occupation = 'Doctor' and r2.rowNo = r1.rowNo) as Doctor,
(select name from repeatedQ as r2 where occupation = 'Professor' and r2.rowNo = r1.rowNo) as Professor,
(select name from repeatedQ as r2 where occupation = 'Singer' and r2.rowNo = r1.rowNo) as Singer,
(select name from repeatedQ as r2 where occupation = 'Actor' and r2.rowNo = r1.rowNo) as Actor
from repeatedQ as r1 group by rowNo;
I searched and try a lot of video but ultimately here my mission get completed... just a fantastic video..
SELECT
[Doctor], [Professor], [Singer], [Actor]
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY NAME ASC) ROW_NUM,
Name,
Occupation
FROM
OCCUPATIONS
) AS SOURCE_TABLE
PIVOT
(
MIN(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor])
) AS PIVOT_TABLE