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.
SELECT
MAX(IF(Occupation = 'Doctor', Name, NULL)) AS Doctor,
MAX(IF(Occupation = 'Professor', Name, NULL)) AS Professor,
MAX(IF(Occupation = 'Singer', Name, NULL)) AS Singer,
MAX(IF(Occupation = 'Actor', Name, NULL)) AS Actor
FROM (
SELECT
@doc := IF(Occupation = 'Doctor', @doc + 1, @doc) AS rn_doc,
@prof := IF(Occupation = 'Professor', @prof + 1, @prof) AS rn_prof,
@sing := IF(Occupation = 'Singer', @sing + 1, @sing) AS rn_sing,
@act := IF(Occupation = 'Actor', @act + 1, @act) AS rn_act,
Name,
Occupation,
CASE Occupation
WHEN 'Doctor' THEN @doc
WHEN 'Professor' THEN @prof
WHEN 'Singer' THEN @sing
WHEN 'Actor' THEN @act
END AS rn
FROM (
SELECT Name, Occupation
FROM OCCUPATIONS
ORDER BY Occupation, Name
) AS sorted,
(SELECT @doc := 0, @prof := 0, @sing := 0, @act := 0) AS vars
) AS numbered
Group By rn
Order By rn;
) AS numbered
GROUP BY rn
ORDER BY rn;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Occupations
You are viewing a single comment's thread. Return to all comments →
Isso funcionou!!
SELECT MAX(IF(Occupation = 'Doctor', Name, NULL)) AS Doctor, MAX(IF(Occupation = 'Professor', Name, NULL)) AS Professor, MAX(IF(Occupation = 'Singer', Name, NULL)) AS Singer, MAX(IF(Occupation = 'Actor', Name, NULL)) AS Actor FROM ( SELECT @doc := IF(Occupation = 'Doctor', @doc + 1, @doc) AS rn_doc, @prof := IF(Occupation = 'Professor', @prof + 1, @prof) AS rn_prof, @sing := IF(Occupation = 'Singer', @sing + 1, @sing) AS rn_sing, @act := IF(Occupation = 'Actor', @act + 1, @act) AS rn_act, Name, Occupation, CASE Occupation WHEN 'Doctor' THEN @doc WHEN 'Professor' THEN @prof WHEN 'Singer' THEN @sing WHEN 'Actor' THEN @act END AS rn FROM ( SELECT Name, Occupation FROM OCCUPATIONS ORDER BY Occupation, Name ) AS sorted, (SELECT @doc := 0, @prof := 0, @sing := 0, @act := 0) AS vars ) AS numbered Group By rn Order By rn; ) AS numbered GROUP BY rn ORDER BY rn;