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

Sort by

recency

|

2190 Discussions

|

  • gajbhiyeks614
     + 1 comment

    why we need row number herencan anybody explain

  • sushmitaluiteld1
     + 0 comments

    SELECT MIN(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MIN(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MIN(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, MIN(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor FROM ( Select Name, occupation, ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name) as RowNum FROM OCCUPATIONS ) AS Temp GROUP BY RowNum ORDER BY RowNum;

  • jr_delaoz
     + 0 comments

    MySQL solution

    SELECT MAX(CASE WHEN occupation = 'Doctor' THEN name ELSE NULL END) AS Doctor, MAX(CASE WHEN occupation = 'Professor' THEN name ELSE NULL END) AS Professor, MAX(CASE WHEN occupation = 'Singer' THEN name ELSE NULL END) AS Singer, MAX(CASE WHEN occupation = 'Actor' THEN name ELSE NULL END) AS Actor FROM ( SELECT name, occupation, ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS row_numb FROM occupations ) AS numbered_rows GROUP BY row_numb ORDER BY row_numb

  • srimurugan972
     + 0 comments

    WITH ordered AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn FROM OCCUPATIONS ) SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor FROM ordered GROUP BY rn ORDER BY rn;

  • srishtirajput201
     + 1 comment

    SELECT COALESCE(MAX(CASE WHEN occupation = 'Doctor' THEN name END), 'NULL'), -- conditional aggregation COALESCE(MAX(CASE WHEN occupation = 'Professor' THEN name END), 'NULL'), -- inside the max function the statement only passes the name if it matches with the occupation otherwise it passes null COALESCE(MAX(CASE WHEN occupation = 'Singer' THEN name END), 'NULL'), COALESCE(MAX(CASE WHEN occupation = 'Actor' THEN name END), 'NULL') FROM ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn -- assigns the sequential row number to each row FROM OCCUPATIONS ) AS t GROUP BY rn -- groups the result by the row number ORDER BY rn; -- order the final result based on the row number