I wanna pick cities from worldcity by these conditions:
- the population of the city exceeds the population of the most populous city in the Filipina. (Meaning: for example city X is the city in the Filipina with the most population. We want to list the cities with a population greater than city X), AND
- there is a city in the United States (uscity) with the same city name as the selected cities
“
worldcity.colomns = ['city', 'lat','lng','country','iso2','iso3','capital','population','id'] uscity.columns = ['city', 'state_id', 'state_name', 'county_fips', 'county_name', 'lat', 'lng', 'population', 'density', 'source', 'military', 'incorporated', 'timezone', 'ranking', 'zips', 'id'] query = """ SELECT DISTINCT city FROM worldcity as w inner join uscity as u on w.city = u.city WHERE w.population >= (MAX)population IN (SELECT population FROM worldcity WHERE country = 'Filipina'; ) """ sql_run(query)
OperationalError: near “population”: syntax error
Advertisement
Answer
Adjust the WHERE
clause to place the MAX
inside the subquery:
... WHERE w.population > ( SELECT MAX(population) FROM worldcity WHERE country = 'Filipina' )