Here’s a quiz for you. The real query I witnessed was very complicated. I’ve simplified it, but kept a confusing alias. Can you answer the following three questions? Are they even valid?
Given the following countries table data:
+----+------+--------------------------+---------------+------------+ | id | Code | Name | Continent | Population | +----+------+--------------------------+---------------+------------+ | 1 | ANT | Netherlands Antilles | North America | 217000 | | 2 | MNP | Northern Mariana Islands | Oceania | 78000 | | 3 | NAM | Namibia | Africa | 1726000 | | 4 | NCL | New Caledonia | Oceania | 214000 | | 5 | NER | Niger | Africa | 10730000 | | 6 | NFK | Norfolk Island | Oceania | 2000 | | 7 | NGA | Nigeria | Africa | 111506000 | | 8 | NIC | Nicaragua | North America | 5074000 | | 9 | NIU | Niue | Oceania | 2000 | | 10 | NLD | Netherlands | Europe | 15864000 | | 11 | NOR | Norway | Europe | 4478500 | | 12 | NPL | Nepal | Asia | 23930000 | | 13 | NRU | Nauru | Oceania | 12000 | | 14 | NZL | New Zealand | Oceania | 3862000 | | 15 | PRK | North Korea | Asia | 24039000 | +----+------+--------------------------+---------------+------------+
Question #1
What is the result of:
SELECT id, Name AS 'n', Continent AS 'c' FROM countries ORDER BY c ;
Question #2
What is the result of:
SELECT MIN(Name) AS 'n', Continent AS 'c' FROM countries GROUP BY c ;
Question #3
What is the result of:
SELECT MIN(Name) AS 'n', Continent AS 'c' FROM countries GROUP BY 'c' ;
Answers and explanations to be followed shortly in a separate post.
I’d expect the first to return the results order by continent first, then essentially some undefined order afterwards.
The second I would expect the country that comes first alphabetically to return with each continent (Namibia with Africa, Nepal with Asia etc.).
The third, I’d expect one row with Namibia and some undefined continent – because you are grouping by the literal string ‘c’.
Is this some trickeration involving the ordering of ENUM values? Or am I barking up the wrong tree?
@kabel
No tricks of this sort. This is an honest SQL question 🙂
@Mark: Douze Point! (You have to be a Eurovision watcher to understand this)