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.
-- 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;
Occupations
You are viewing a single comment's thread. Return to all 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;