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.
- Prepare
- SQL
- Advanced Select
- Occupations
- Discussions
Occupations
Occupations
Sort by
recency
|
2040 Discussions
|
Please Login in order to post a comment
The solution is at this Carbon link
set @r_Doctor = 0, @r_Professor = 0,@r_Singer = 0,@r_Actor = 0; select min(Doctor),min(Professor),min(Singer),min(Actor) from( select case when OCCUPATION = 'Doctor' then (@r_Doctor:= @r_Doctor + 1) when OCCUPATION = 'Professor' then (@r_Professor:= @r_Professor + 1) when OCCUPATION = 'Singer' then (@r_Singer:= @r_Singer + 1) when OCCUPATION = 'Actor' then (@r_Actor:= @r_Actor + 1) end as row_num, case when OCCUPATION = 'Doctor' then name else NULL end as Doctor, case when OCCUPATION = 'Professor' then name else NULL end as Professor, case when OCCUPATION = 'Singer' then name else NULL end as Singer, case when OCCUPATION = 'Actor' then name else NULL end as Actor from OCCUPATIONS order by name ) as tbl group by row_num;
set @r_Doctor = 0, @r_Professor = 0,@r_Singer = 0,@r_Actor = 0; select min(Doctor),min(Professor),min(Singer),min(Actor) from( select case when OCCUPATION = 'Doctor' then (@r_Doctor:= @r_Doctor + 1) when OCCUPATION = 'Professor' then (@r_Professor:= @r_Professor + 1) when OCCUPATION = 'Singer' then (@r_Singer:= @r_Singer + 1) when OCCUPATION = 'Actor' then (@r_Actor:= @r_Actor + 1) end as row_num, case when OCCUPATION = 'Doctor' then name else NULL end as Doctor, case when OCCUPATION = 'Professor' then name else NULL end as Professor, case when OCCUPATION = 'Singer' then name else NULL end as Singer, case when OCCUPATION = 'Actor' then name else NULL end as Actor from OCCUPATIONS order by name ) as tbl group by row_num;
SELECT CONCAT(GROUP_CONCAT(IF(Occupation = 'Doctor', Name, NULL) ORDER BY Name SEPARATOR ',')) AS Doctor, CONCAT(GROUP_CONCAT(IF(Occupation = 'Professor', Name, NULL) ORDER BY Name SEPARATOR ',')) AS Professor, CONCAT(GROUP_CONCAT(IF(Occupation = 'Singer', Name, NULL) ORDER BY Name SEPARATOR ',')) AS Singer, CONCAT(GROUP_CONCAT(IF(Occupation = 'Actor', Name, NULL) ORDER BY Name SEPARATOR ',')) AS Actor FROM OCCUPATIONS; CAN SOMBODY CORRECT IT
select min(if(occupation='Doctor',name,null)),min(if(occupation='Professor',name,null)),min(if(occupation='Singer',name,null)),min(if(occupation='Actor',name,null)) from (select row_number() over(partition by occupation order by name) as rowss, name,occupation from occupations )as new group by rowss;