#22 Occupations | HackerRank SQL Solutions

[@kiranvenkat7909]

Of all the videos out there about this problem, you are the one who explained it clearly. Great job. Do more such videos.

[@UrbanForrest95]

This is well explained out of all the videos available on KZbin for this particular problem, Thanks

[@antibioticsOfWorld]

literally, u r the only one who has explained ...rest of them have just read the code as it is ..well done

[@rishiraj1028]

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

[@Stayin_delulu_is_the_soluluu_]

as ord (before group by) last line mein kyun ?

[@mrinalbisht4212]

@@Stayin_delulu_is_the_soluluu_ to declare as a temp TABLE with name ord, which we used further

[@katerirakim3690]

wonderful explanation ! well understood especially how min and max function works in here thx!!!

[@dev.19.community]

Great to hear!

[@Mustafa-099]

Why are we using the aggregate function Min() for each of the if condition for occupations?

[@DSCHsaxen]

to use group by we have to make it compulsory to use an agrregate function while selecting

[@sanskarkurude]

very nice explanation bro

[@sahilmothsara8641]

Good work broo....keep it up.....try to use language in which you are more comfortable....just a suggestion

[@ishasingh9906]

Why max function is not returning the maximum row_number ?

[@satyadharkumarchintagunta3793]

So well explained!!!!!!!Thank You

[@muskanmamta4347]

wonderfull

[@MinChitXD]

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? ❤❤

[@dev.19.community]

Without GroupBy also we can use min max , no issue

[@cyber_dbs]

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;

[@akshatchaurasia1079]

Very helpful

[@souravpramanik6740]

thank you very much.....❤

[@vaibhavsrivastava7945]

Thankyou bro it helped me

[@HazeulHajar]

thank you so much

[@mdhalima5682]

thankyou sooo much

[@ravikantchauhan2106]

I searched and try a lot of video but ultimately here my mission get completed... just a fantastic video..

[@Sattya998]

❤❤❤❤❤❤❤❤❤❤❤❤❤❤