how to solve the below CTE syntax error for the mentioned query-CodePudding

I have a sample query. I have tried to rewrite it with CTE but some errors occurred. Please check and solve the below query.

actual query;

select 
  dt.population, 
  dt.name, 
  c.name 
from 
  (
    select 
      distinct countrycode, 
      name 
    from 
      city
  ) as c, 
  lateral(
    select 
      name, 
      population 
    from 
      city 
    where 
      city.countrycode = c.countrycode 
    order by 
      population desc 
    limit 
      1
  ) as dt\G

with CTE

 with cte1  as (
    select 
      distinct countrycode, 
      name 
    from 
      city 
  ), 
     cte2  as (
    select 
      name, 
      population 
    from 
      city 
    where 
      cte2.countrycode = cte1.countrycode 
    order by 
      population desc 
    limit 
      1
  ) 
select 
  population, 
  name, 
  name 
from 
  ( select * 

  from cte1,cte2
  ) as a \G

error : ERROR 1060 (42S21): Duplicate column name 'name' ERROR 1054 (42S22): Unknown column 'cte2.countrycode' in 'where clause'

i have tried put alias for all the table but still am missing something.

CodePudding user response：

Refer to this to know where you've define wrong:

with cte1  as (
   select 
     distinct countrycode, 
     name 
   from 
     city 
 ), 
    cte2  as (
   select 
     name, 
     population 
   from 
     city 
   where 
     cte2.countrycode = cte1.countrycode /*wrong*/
   order by 
     population desc 
   limit 
     1
 ) 
select 
 population, 
 name, /* wr    */
 name  /*   ong */
from 
 ( select * 

 from cte1,cte2
 ) as a \G /*wrong*/

This is a modification that is similar to what you've attempted - without error - but I'm pretty sure it's not what you intended:

 with cte1  as (
    select distinct countrycode, name 
    from city 
  ), 
     cte2  as (
    select name, population, countrycode
    from city 
    order by population desc 
    limit 1
  ) 
select 
  population, cte1.name, cte2.name 
from  cte1, cte2
    where 
      cte2.countrycode = cte1.countrycode;

Without sample data and expected result, I can only assume that this is what you actually want to do:

WITH cte1  AS (
    SELECT DISTINCT countrycode, NAME 
    FROM city 
  ), 
     cte2  AS (
    SELECT NAME, population, countrycode,
           ROW_NUMBER() OVER (PARTITION BY countrycode ORDER BY population DESC) AS Rn
    FROM city 
  )
SELECT 
  cte2.population, cte2.name, cte1.name
FROM  cte1
  JOIN cte2
    ON cte2.countrycode = cte1.countrycode
  WHERE Rn=1;

I've tested that with your original query (without cte) in this fiddle with some sample data and it does return the same result.

CodePudding user response：

The error means that every sub query must have an alias. So in your second request you have to add an alias for the last subquery

select 
population, 
name, 
name 
from 
  ( select * 

  from cte1,cte2
  ) as subqueryName

I will assume that the \G is a typo, if not you have to remove it.

EDIT:

I don't really understand the query but this seems to work, can you try it and see if the output is what you expect.

    with cte1  as (
 select 
      distinct(c.countrycode), 
      name 
    from 
      city c
  ), 
     cte2  as (
    select 
      name, 
      population 
    from 
      city ct
    order by 
      population desc 
    limit 
      1
  )

    select 
      population, 
      name1, 
      name2
    from 
      ( select cte1.name name1, cte2.name name2, cte2.population
      from cte1,cte2
      ) as nameSubquery