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

  • silver_waffle
     + 0 comments

    MS SQL SERVER SOLUTION :

    SELECT  
    MAX(CASE WHEN OCCUPATION = "DOCTOR" THEN NAME END),
    MAX(CASE WHEN OCCUPATION = "PROFESSOR" THEN NAME END),
    MAX(CASE WHEN OCCUPATION = "SINGER" THEN NAME END),
    MAX(CASE WHEN OCCUPATION = "ACTOR" THEN NAME END) 
FROM 
    (SELECT NAME, OCCUPATION, 
     ROW_NUMBER() 
     OVER(
     PARTITION BY OCCUPATION 
     ORDER BY NAME
     ) AS RNK
     FROM OCCUPATIONS
    )
AS TABLE_WITH_RANKS
GROUP BY RNK;

    explanation :

    (SELECT NAME, OCCUPATION, 
     ROW_NUMBER() 
     OVER(
     PARTITION BY OCCUPATION 
     ORDER BY NAME
     ) AS RNK
     FROM OCCUPATIONS
    )
AS TABLE_WITH_RANKS

    this query provides a table(table_with_ranks) that looks like :

    Name	            Occupation	           rnk
Maria	              Actor	                        1
Jane	               Actor	                       2
Julia	                Actor	                        3
Jenny	             Doctor	                      1
Samantha	    Doctor	                     2
Ashely	          Professor	                  1
Christeen	      Professor                 	2
Ketty	              Professor	                  3
Meera	            Singer	                       1
Priya	              Singer	                        2

    Here, we have columns- name, occupation and rank (generated for each occupation using ROW_NUMBER() function) Since 1st it is partitioned by occupation, it gives values sorted in asc order according to occupation and then sorts the names as asc order.

    Now, this table is used to query :

    MAX(CASE WHEN OCCUPATION = "DOCTOR" THEN NAME END),
MAX(CASE WHEN OCCUPATION = "PROFESSOR" THEN NAME END),
MAX(CASE WHEN OCCUPATION = "SINGER" THEN NAME END),
MAX(CASE WHEN OCCUPATION = "ACTOR" THEN NAME END)

    MAX on a string datatype column gives the maximum order name - in doctor occupation we have names = Jenny and samantha, so SAMANTHA is selected here, similarly for PROFESSOR we have KETTY, for SINGER we have PRIYA, for Actor we have JULIA

    and then there is a GROUP BY RNK, SO, the table formed here will be :

    RANK  Doctor	    Professor	    Singer	        Actor
1	        Maria	      Jenny	           Ashely	        Meera
2	        Jane	       Samantha	   Christeen	  Priya
3	        Julia	        NULL	           Ketty	          NULL

    and this is the desired solution !

    `