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.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. Occupations
  5. Discussions

Occupations

  • M_F_H
     + 1 comment

    very nice solution. minor "improvements" (maybe question of taste...): using "if()" is a little shorter than case...end; I find "max" mor meaningful than min. Thus:

    set @a=0, @b=0, @c=0, @d=0;
select max(D), max(P), max(S), max(A) from (
  select case when Occupation='Doctor'    then (@a:=@a+1)
              when Occupation='Professor' then (@b:=@b+1)
              when Occupation='Singer'    then (@c:=@c+1)
              when Occupation='Actor'     then (@d:=@d+1) end as Row,
    if( Occupation='Doctor'   , Name, NULL) as D,
    if( Occupation='Professor', Name, NULL) as P,
    if( Occupation='Singer'   , Name, NULL) as S,
    if( Occupation='Actor'    , Name, NULL) as A
  from OCCUPATIONS
  order by Name
) t
group by Row