#22 Occupations | HackerRank SQL Solutions

Please follow us
/ dev.19.community
OVER ([PARTITION BY columns] [ORDER BY columns])
learnsql.com/blog/sql-over-cl...
Thanks for watching us ...
Dev19🖤

[@sanskarkurude]

very nice explanation bro

[@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 TH-cam 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

[@satyadharkumarchintagunta3793]

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

[@katerirakim3690]

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

[@dev.19.community]

Great to hear!

[@muskanmamta4347]

wonderfull

[@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

[@ishasingh9906]

Why max function is not returning the maximum row_number ?

[@vaibhavsrivastava7945]

Thankyou bro it helped me

[@souravpramanik6740]

thank you very much.....❤

[@sahilmothsara8641]

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

[@HazeulHajar]

thank you so much

[@akshatchaurasia1079]

Very helpful

[@mdhalima5682]

thankyou sooo much

[@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

[@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

[@Sattya998]

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

[@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;

[@ravikantchauhan2106]

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

[@lukmanril]

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