CODEWITHSUNDEEP
mysqlsql
MV_81
MV_81

Reputation: 111

MySQL World database query

I'm running some queries and not getting expected results.

SELECT Name, Population FROM City
WHERE Population = (SELECT Min(Population) FROM City);

Which brings me Adamstown 42.

Then I tried this (not being really sure about it)

SELECT Name, Min(Population) FROM City;

And it brings Kabul 42.

I understand first query OK, but I don't get what second query should bring or if it is useful at all.

Upvotes: 0

Views: 2086

Answers (3)

Luke
Luke

Reputation: 1724

I just downloaded the dataset to take a look.

The first query is correctly working out that the minimum population is 42, and then returning all rows with population 42 (a single row fits this condition).

The second query is determining the minimum population is 42, and then needs to return some value in the 'name' column. Unfortunately, we haven't grouped by name (or anything); the rows in the group (all in the table) each have their own name. Rather than spit out an error, mySQL seems to be returning the name of the first row; Kabul.

But obviously, this second query is a bit meaningless. The first query is preferable.

This behaviour is actually described in the documentation:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. ... The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Upvotes: 3

Tim3880
Tim3880

Reputation: 2583

From MySQL document: http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

To get the city with least population, you can use your first query.

Upvotes: 1

Rahul
Rahul

Reputation: 77896

There are subtle differences between the queries you have posted.

First one:

SELECT Name, Population FROM City
WHERE Population = (SELECT Min(Population) FROM City);

You are trying to fetch a record where Population = Min(population); which will in turn get evaluated as Population = 42

Second one:

SELECT Name, Min(Population) FROM City;

You are just trying to fetch Name, and minimum population which doesn't mean that particular record hold minimum population.

Upvotes: 1

Related Questions