Students Report By Geography


  • 0

    Click here to see the full article post


  • 0
    P

    May I know if case when can be used here?
    I try to do:

    SELECT (CASE WHEN continent = 'America' then name else NULL END) AS America, (CASE WHEN continent = 'Asia' then name else NULL END) AS Asia, (CASE WHEN continent = 'Europe' then name else NULL END) AS Europe FROM student
    

    But I don't know how to remove the null values


  • 0

    @pxj5333 I don't think you can use case...when.... Please check the editorial solution instead.


  • 0
    V

    SELECT a.america, b.asia, c.europe
    FROM (SELECT ROWNUM rid, name AS America
    FROM student
    WHERE continent = 'America') A,
    (SELECT ROWNUM rid, name AS Asia
    FROM student
    WHERE continent = 'Asia') B,
    (SELECT ROWNUM rid, name AS Europe
    FROM student
    WHERE continent = 'Europe') C
    WHERE a.rid = b.rid(+) AND b.rid = c.rid(+);


  • 0
    G

    well, and what to do if we have not continents, but countries or cities? Wall of constants doesn't look like a solution


  • 0
    N

    @vineeshbnair unknown column ROWNUM error


  • 0
    M

    Easier to use window function row_number() with PostgreSQL:

    SELECT
      America,
      Asia,
      Europe
    FROM
      (SELECT
         row_number()
         OVER (PARTITION BY continent) AS asid,
         name                          AS Asia
       FROM
         student
       WHERE
         continent = 'Asia'
       ORDER BY Asia) AS t1
      RIGHT JOIN
      (SELECT
         row_number()
         OVER (PARTITION BY continent) AS amid,
         name                          AS America
       FROM
         student
       WHERE
         continent = 'America'
       ORDER BY America) AS t2 ON asid = amid
      LEFT JOIN
      (SELECT
         row_number()
         OVER (PARTITION BY continent) AS euid,
         name                          AS Europe
       FROM
         student
       WHERE
         continent = 'Europe'
       ORDER BY Europe) AS t3 ON amid = euid;
    

  • 0
    C

    @pxj5333 case ...... when ... will always give you null values in this case, so it is not a good solution here.


  • 0

    with dataset as (
    select row_number() over (partition by continent order by continent) rid,continent,name from student
    )
    select
    decode(am.continent,'America',am.name,null) as "America"
    ,decode(asia.continent,'Asia',asia.name,null) as "asia"
    ,decode(eu.continent,'Europe',eu.name,null) as "Europe"
    from
    (select * from dataset where continent='Europe' ) eu
    full join
    (select * from dataset where continent='America' ) am
    on eu.rid=am.rid
    full join
    (select * from dataset where continent='Asia' ) asia
    on eu.rid=asia.rid


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.