We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  • Hackerrank Home
  • Practice
  • Certification
  • Compete
  • Career Fair
  • Hiring developers?
  1. Practice
  2. SQL
  3. Advanced Select
  4. Occupations
  5. Discussions

Occupations

Problem
Submissions
Leaderboard
Discussions

Sort 876 Discussions, By:

votes

Please Login in order to post a comment

  • YujiShen
    5 years ago+ 50 comments

    My answer in MySQL. I have to keep track of row number of each Occupation for pivoting. Using either max()/min() can get first non-null value after group by.

    set @r1=0, @r2=0, @r3=0, @r4=0;
    select min(Doctor), min(Professor), min(Singer), min(Actor)
    from(
      select case when Occupation='Doctor' then (@r1:=@r1+1)
                when Occupation='Professor' then (@r2:=@r2+1)
                when Occupation='Singer' then (@r3:=@r3+1)
                when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
        case when Occupation='Doctor' then Name end as Doctor,
        case when Occupation='Professor' then Name end as Professor,
        case when Occupation='Singer' then Name end as Singer,
        case when Occupation='Actor' then Name end as Actor
      from OCCUPATIONS
      order by Name
    ) Temp
    group by RowNumber
    
    244|
    Permalink
    View more Comments..
  • raiyanger24
    1 year ago+ 17 comments

    Let me break it down in steps (answer in MySQL)

    Step 1:

    Create a virtual table in your head of the data given to us. It look like this https://imgur.com/u6DEcNQ

    SELECT
        case when Occupation='Doctor' then Name end as Doctor,
        case when Occupation='Professor' then Name end as Professor,
        case when Occupation='Singer' then Name end as Singer,
        case when Occupation='Actor' then Name end as Actor
    FROM OCCUPATIONS
    

    Step 2:

    Create an index column with respect to occupation as "RowNumber".https://imgur.com/QzVCWFn

    Notice from the image, under professor column, the first Name is indexed as 1, the next name "Birtney" as 2. That is what I mean by index w.r.t occupation.

    The below code will only give the "RowNumber" column, to get the result like in image proceed to step 3.

    set @r1=0, @r2=0, @r3=0, @r4=0;
    
    SELECT case 
    	when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber
    
    FROM OCCUPATIONS
    

    Step 3:

    Combine the result from step 1 and step 2:

    set @r1=0, @r2=0, @r3=0, @r4=0;
    
    SELECT case 
    	when Occupation='Doctor' then (@r1:=@r1+1)
            when Occupation='Professor' then (@r2:=@r2+1)
            when Occupation='Singer' then (@r3:=@r3+1)
            when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
            case when Occupation='Doctor' then Name end as Doctor,
            case when Occupation='Professor' then Name end as Professor,
            case when Occupation='Singer' then Name end as Singer,
            case when Occupation='Actor' then Name end as Actor
    
    FROM OCCUPATIONS
    

    Step 4:

    Now, Order_by name then Group_By RowNumber.

    Using Min/Max, if there is a name, it will return it, if not, return NULL.

    set @r1=0, @r2=0, @r3=0, @r4=0;
    select min(Doctor), min(Professor), min(Singer), min(Actor)
    from(
      select case when Occupation='Doctor' then (@r1:=@r1+1)
                when Occupation='Professor' then (@r2:=@r2+1)
                when Occupation='Singer' then (@r3:=@r3+1)
                when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
        case when Occupation='Doctor' then Name end as Doctor,
        case when Occupation='Professor' then Name end as Professor,
        case when Occupation='Singer' then Name end as Singer,
        case when Occupation='Actor' then Name end as Actor
      from OCCUPATIONS
      order by Name
    	) temp
    group by RowNumber;
    

    **EDIT** I can see many asking why MIN or temp?

    temp - Since I created a temporary table inside the query, I have to give it an alise. It is a good practise.

    Why MIN in the select statement? Since some of us here may not be fimilar with sql, I'll start with where I left so you get the whole picture.

    1. Once you complete step 3, add "ORDER BY Name" (Refer above code on where to add Order by clause). The result will look like this https://imgur.com/aBHUqN6

    What changed? the names in all four columns are sorted as per alphabetical order.

    1. Now, we only want the names and not the NULL values from our virtual table. How can we do that? - There maybe be multiple ways, lets us consider the MIN/MAX (Yes, you can replace MIN with MAX and you will get the same result)

    2. Without GROUP BY clause - When a MIN/MAX is used in a Select statement, it will return The "LOWEST" element from each column (which happened to be the first element because we used ORDER BY, if you use MAX, you will get the last element from each column). It will look like this https://imgur.com/XDZzc4Z That means, you will always get a single row from a table.

    SET @r1=0,@r2=0,@r3=0,@r4=0;
    SELECT MIN(Doctor),MIN(Professor),MIN(Singer),MIN(Actor)
    
    FROM (
    SELECT CASE
        WHEN OCCUPATION = 'Doctor' THEN (@r1:=@r1+1)
        WHEN OCCUPATION = 'Professor' THEN (@r2:=@r2+1)
        WHEN OCCUPATION = 'Singer' THEN (@r3:=@r3+1)
        WHEN OCCUPATION = 'Actor' THEN (@r4:=@r4+1) END AS RowNumber,
        CASE WHEN OCCUPATION = 'Doctor' THEN Name END AS Doctor,
        CASE WHEN OCCUPATION = 'Professor' THEN Name END AS Professor,
        CASE WHEN OCCUPATION = 'Singer' THEN Name END AS Singer,
        CASE WHEN OCCUPATION = 'Actor' THEN Name END AS Actor
    FROM OCCUPATIONS
    ORDER BY Name) as temp
    
    1. With GROUP BY clause - The result set will have one row for EACH group (which is RowNumber in our case).
    127|
    Permalink
    View more Comments..
  • nasifra
    5 years ago+ 9 comments

    Here is my solution in MS SQL Server:

    SELECT
        [Doctor], [Professor], [Singer], [Actor]
    FROM
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) [RowNumber], * FROM OCCUPATIONS
    ) AS tempTable
    PIVOT
    (
        MAX(NAME) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])
    ) AS pivotTable
    
    113|
    Permalink
    View more Comments..
  • nobuh
    4 years ago+ 8 comments
    -- MySQL
    SELECT 
    MIN(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,
    MIN(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,
    MIN(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,
    MIN(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor
    FROM (
      SELECT a.Occupation,
             a.Name,
             (SELECT COUNT(*) 
                FROM Occupations AS b
                WHERE a.Occupation = b.Occupation AND a.Name > b.Name) AS rank
      FROM Occupations AS a
    ) AS c
    GROUP BY c.rank;
    
    66|
    Permalink
    View more Comments..
  • mannu_singhal
    5 years ago+ 8 comments

    *Trying without PIVOT *

    Select D.Name, P.Name, S.Name, A.Name

    from

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Doctor') D

    full outer join

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Professor') P on D.id = P.id

    full outer join

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Singer') S on P.id = S.id

    full outer join

    (Select Name, row_number() over (partition by occupation order by name) id from Occupations where Occupation = 'Actor') A on S.id = A.id;

    35|
    Permalink
    View more Comments..
Load more conversations

Need Help?


View top submissions
  • Contest Calendar
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy
  • Request a Feature