0

I am playing with the city table on the world database in MYSQL and I am having some strange results which I know are wrong. I want to know why that is happening and how to solve the situation.

select max(population) from city
## answer##
10500000 ##

which is the population of mumbai and when try to tie name to it here is what I get:

select, name, max(population) from city
## answer##
Kabul   10500000 ### which is not the population Kabul###
Camue
  • 469
  • 7
  • 17
  • https://stackoverflow.com/a/33629201/5070879 `The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate` - Long story short, for every major RDBMS second query is invalid. – Lukasz Szozda Jul 14 '19 at 13:44
  • 1
    If you only need a single row as result, then use a (trivial) `ORDER BY ... LIMIT 1` query. – Paul Spiegel Jul 14 '19 at 14:01
  • You haven't said what you do want.. – P.Salmon Jul 14 '19 at 14:04

2 Answers2

0

This query:

select, name, max(population)
from city

does not make sense. It is an aggregation query (because of the max()). It will return exactly one row (because there is no group by). But what value of name?

Well, in standard SQL, almost all databases, and the most recent versions of MySQL (with default settings), it returns an error, to the effect of "name is not being aggregated".

Older versions of MySQL allow this construct, and choose a value of name from an arbitrary and indeterminate row in the data.

What you seem to really want is:

select name, population
from city
order by population desc
limit 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

The correct syntax for your second query would be - 

SELECT name, MAX(population)
FROM city
GROUP BY name;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40