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

  • czaryg5000
     + 0 comments

    I used a similar query, but my order for the professors is incorrect.

    SELECT DocTable.NAME, ProfTable.NAME, SingTable.NAME, ActTable.NAME FROM (SELECT OCCUPATION, [NAME], ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY [NAME] ASC) AS ID FROM OCCUPATIONS WHERE OCCUPATION = 'Doctor') As DocTable FULL OUTER JOIN (SELECT OCCUPATION, [NAME], ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY [NAME] ASC) AS ID FROM OCCUPATIONS WHERE OCCUPATION = 'Actor') As ActTable ON DocTable.ID = ActTable.ID FULL OUTER JOIN (SELECT OCCUPATION, [NAME], ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY [NAME] ASC) AS ID FROM OCCUPATIONS WHERE OCCUPATION = 'Professor') As ProfTable ON ActTable.ID = ProfTable.ID FULL OUTER JOIN (SELECT OCCUPATION, [NAME], ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY [NAME] ASC) AS ID FROM OCCUPATIONS WHERE OCCUPATION = 'Singer') As SingTable ON ProfTable.ID = SingTable.ID