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

  • raiyanger24
     + 76 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).