{"id":246,"date":"2008-12-01T18:16:51","date_gmt":"2008-12-01T16:16:51","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=246"},"modified":"2008-12-13T06:33:43","modified_gmt":"2008-12-13T04:33:43","slug":"selecting-a-specific-non-aggregated-column-data-in-group-by","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/selecting-a-specific-non-aggregated-column-data-in-group-by","title":{"rendered":"Selecting a specific non aggregated column data in GROUP BY"},"content":{"rendered":"

In a GROUP BY query, MySQL may allow specifying non aggregated columns. For example, using MySQL’s world database<\/a>, I wish to get the number of countries per continent, along with a “sample” country:<\/p>\n

\n
SELECT <\/strong>Continent, COUNT<\/strong>(*), Name\r\nFROM <\/strong>`Country` GROUP BY <\/strong>Continent\r\n\r\n+---------------+----------+----------------+\r\n| Continent     | COUNT(*) | Name           |\r\n+---------------+----------+----------------+\r\n| Asia          |       51 | Afghanistan    |\r\n| Europe        |       46 | Albania        |\r\n| North America |       37 | Aruba          |\r\n| Africa        |       58 | Angola         |\r\n| Oceania       |       28 | American Samoa |\r\n| Antarctica    |        5 | Antarctica     |\r\n| South America |       14 | Argentina      |\r\n+---------------+----------+----------------+<\/pre>\n<\/blockquote>\n
What if I want to choose that “sample” country? For example, for each continent, I wish to show the country with the largest population. To simply see the largest population, I would use MAX(Population). But which country is referred? I wish to provide a solution which does not involve sub-queries, HAVING or JOINs.<\/p>\n
We begin by observing MySQL’s GROUP_CONCAT() fcuntion:<\/p>\n
\n
SELECT <\/strong>Continent, COUNT<\/strong>(*), GROUP_CONCAT<\/strong>(Name)\r\nFROM <\/strong>`Country` GROUP BY <\/strong>Continent\r\n\r\n+---------------+----------+-------------------------------+\r\n| Continent     | COUNT(*) | GROUP_CONCAT(Name)            |\r\n+---------------+----------+-------------------------------+\r\n| Asia          |       51 | Sri Lanka,South Korea,Mald... |\r\n| Europe        |       46 | Iceland,Faroe Islands,Fran... |\r\n| North America |       37 | Martinique,Mexico,Puerto R... |\r\n| Africa        |       58 | Zambia,South Africa,Nigeri... |\r\n| Oceania       |       28 | Tokelau,American Samoa,Sam... |\r\n| Antarctica    |        5 | South Georgia and the Sout... |\r\n| South America |       14 | Suriname,Argentina,Ecuador... |\r\n+---------------+----------+-------------------------------+<\/pre>\n<\/blockquote>\n
Mmmm. The order by which the names are presented does not seem to be of use to me. Didn’t we say we want to see the country with the largest population? It just so happens that GROUP_CONCAT allows for ORDER BY:<\/p>\n
\n
SELECT <\/strong>Continent, COUNT<\/strong>(*),\r\n  GROUP_CONCAT<\/strong>(Name ORDER BY <\/strong>Population DESC<\/strong>)\r\nFROM <\/strong>`Country` GROUP BY <\/strong>Continent\r\n\r\n+---------------+----------+-------------------------------------------------------+\r\n| Continent     | COUNT(*) | GROUP_CONCAT(Name ORDER BY Population DESC)           |\r\n+---------------+----------+-------------------------------------------------------+\r\n| Asia          |       51 | China,India,Indonesia,Pakistan,Bangladesh,Japan,Vi... |\r\n| Europe        |       46 | Russian Federation,Germany,United Kingdom,France,I... |\r\n| North America |       37 | United States,Mexico,Canada,Guatemala,Cuba,Dominic... |\r\n| Africa        |       58 | Nigeria,Egypt,Ethiopia,Congo, The Democratic Repub... |\r\n| Oceania       |       28 | Australia,Papua New Guinea,New Zealand,Fiji Island... |\r\n| Antarctica    |        5 | Heard Island and McDonald Islands,Antarctica,Bouve... |\r\n| South America |       14 | Brazil,Colombia,Argentina,Peru,Venezuela,Chile,Ecu... |\r\n+---------------+----------+-------------------------------------------------------+<\/pre>\n<\/blockquote>\n
Good progress! We can now see our desired countries first in list. What’s next? We want to isolate the first country for each continent. SUBSTRING_INDEX() comes to the rescue. Finally, we reach the following query:<\/p>\n
\n
SELECT <\/strong>Continent, COUNT<\/strong>(*),\r\n  SUBSTRING_INDEX<\/strong>(\r\n    CONCAT<\/strong>(\r\n      GROUP_CONCAT<\/strong>(Name ORDER BY <\/strong>Population DESC<\/strong>),\r\n      ','),\r\n    ',', 1) AS <\/strong>largest_country\r\nFROM <\/strong>`Country` GROUP BY <\/strong>Continent\r\n\r\n+---------------+----------+-----------------------------------+\r\n| Continent     | COUNT(*) | largest_country                   |\r\n+---------------+----------+-----------------------------------+\r\n| Asia          |       51 | China                             |\r\n| Europe        |       46 | Russian Federation                |\r\n| North America |       37 | United States                     |\r\n| Africa        |       58 | Nigeria                           |\r\n| Oceania       |       28 | Australia                         |\r\n| Antarctica    |        5 | Heard Island and McDonald Islands |\r\n| South America |       14 | Brazil                            |\r\n+---------------+----------+-----------------------------------+<\/pre>\n<\/blockquote>\n
This query provides us with the right answer. No sub-selects, no joins required. Granted, it’s not too pretty, and it’s not clean SQL (it’s not even standard<\/em> SQL) – but it just may save some query time: there’s an index to add which will cause this query to run “using index”:<\/p>\n
\n
ALTER TABLE <\/strong>Country ADD INDEX <\/strong>`Continent_Name_Population` (`Continent`,`name`,`Population`)<\/pre>\n<\/blockquote>\n
Notes<\/h4>\n