{"id":3164,"date":"2011-01-06T12:49:05","date_gmt":"2011-01-06T10:49:05","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3164"},"modified":"2012-08-21T06:58:59","modified_gmt":"2012-08-21T04:58:59","slug":"sql-selecting-top-n-records-per-group","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-selecting-top-n-records-per-group","title":{"rendered":"SQL: selecting top N records per group"},"content":{"rendered":"<p>A while back I <a href=\"http:\/\/code.openark.org\/blog\/mysql\/selecting-a-specific-non-aggregated-column-data-in-group-by\">presented<\/a>(*) an SQL trick to present with non-aggregated column on a GROUP BY query, without use of subquery or derived tables.<\/p>\n<p>Based on a similar concept, combined with string walking, I now present a query which selects top-n records for each group, ordered by <em>some condition<\/em>. It will require no subqueries. It executes faster than its more conventional alternatives.<\/p>\n<p>[UPDATE: this is MySQL only. Others can use Window Functions where available]<\/p>\n<p>Using the simple <a href=\"http:\/\/dev.mysql.com\/doc\/index-other.html\">world database<\/a>, we answer the following question:<\/p>\n<blockquote><p>What are the top 5 largest (by area) countries for each continent? What are their names, surface area and population?<\/p><\/blockquote>\n<p>Similar questions would be:<\/p>\n<blockquote><p>What were the latest 5 films rented by each customer?<\/p>\n<p>What were the most presented advertisements for each user?<\/p><\/blockquote>\n<p>etc.<\/p>\n<h4>Step 1: getting the top<\/h4>\n<p>We already know how to get a single column&#8217;s value for the top country, as presented in the aforementioned post:<!--more--><\/p>\n<blockquote>\n<pre>SELECT\r\n Continent,\r\n SUBSTRING_INDEX(\r\n   GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),\r\n   ',', 1) AS Name\r\nFROM\r\n Country\r\nGROUP BY\r\n Continent\r\n;\r\n+---------------+--------------------+\r\n| Continent\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------+--------------------+\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | China\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Russian Federation |\r\n| North America | Canada\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Sudan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Australia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| Antarctica\u00a0\u00a0\u00a0 | Antarctica\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| South America | Brazil\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+---------------+--------------------+<\/pre>\n<\/blockquote>\n<h4>Step 2: adding columns<\/h4>\n<p>This part is easy: just throw in the rest of the columns (again, only indicating the top country in each continent)<\/p>\n<blockquote>\n<pre>SELECT\r\n Continent,\r\n SUBSTRING_INDEX(\r\n   GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),\r\n   ',', 1) AS Name,\r\n SUBSTRING_INDEX(\r\n   GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),\r\n   ',', 1) AS SurfaceArea,\r\n SUBSTRING_INDEX(\r\n   GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),\r\n   ',', 1) AS Population\r\nFROM\r\n Country\r\nGROUP BY\r\n Continent\r\n;\r\n\r\n+---------------+--------------------+-------------+------------+\r\n| Continent\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | SurfaceArea | Population |\r\n+---------------+--------------------+-------------+------------+\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | China\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 9572900.00\u00a0 | 1277558000 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Russian Federation | 17075400.00 | 146934000\u00a0 |\r\n| North America | Canada\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 9970610.00\u00a0 | 31147000\u00a0\u00a0 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Sudan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2505813.00\u00a0 | 29490000\u00a0\u00a0 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Australia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 7741220.00\u00a0 | 18886000\u00a0\u00a0 |\r\n| Antarctica\u00a0\u00a0\u00a0 | Antarctica\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 13120000.00 | 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| South America | Brazil\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 8547403.00\u00a0 | 170115000\u00a0 |\r\n+---------------+--------------------+-------------+------------+<\/pre>\n<\/blockquote>\n<h4>Step 3: casting<\/h4>\n<p>You&#8217;ll notice that the <strong>Population<\/strong> column from this last execution is aligned to the left. This is because it is believed to be a string. The <strong>GROUP_CONCAT<\/strong> clause concatenates values in one string, and <strong>SUBSTRING_INDEX<\/strong> parses a substring. The same applies to the SurfaceArea column. We&#8217;ll cast <strong>Population<\/strong> as <strong>UNSIGNED<\/strong> and <strong>SurfaceArea<\/strong> as <strong>DECIMAL<\/strong>:<\/p>\n<blockquote>\n<pre>SELECT\r\n  Continent,\r\n  SUBSTRING_INDEX(\r\n    GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),\r\n    ',', 1) AS Name,\r\n  CAST(\r\n    SUBSTRING_INDEX(\r\n      GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),\r\n      ',', 1)\r\n    AS DECIMAL(20,2)\r\n    ) AS SurfaceArea,\r\n  CAST(\r\n    SUBSTRING_INDEX(\r\n      GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),\r\n      ',', 1)\r\n    AS UNSIGNED\r\n    ) AS Population\r\nFROM\r\n Country\r\nGROUP BY\r\n Continent\r\n;\r\n+---------------+--------------------+-------------+------------+\r\n| Continent\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | SurfaceArea | Population |\r\n+---------------+--------------------+-------------+------------+\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | China\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 9572900.00 | 1277558000 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Russian Federation | 17075400.00 |\u00a0 146934000 |\r\n| North America | Canada\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 9970610.00 |\u00a0\u00a0 31147000 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Sudan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2505813.00 |\u00a0\u00a0 29490000 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Australia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 7741220.00 |\u00a0\u00a0 18886000 |\r\n| Antarctica\u00a0\u00a0\u00a0 | Antarctica\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 13120000.00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| South America | Brazil\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 8547403.00 |\u00a0 170115000 |\r\n+---------------+--------------------+-------------+------------+<\/pre>\n<\/blockquote>\n<h4>Step 4: top n records<\/h4>\n<p>It&#8217;s time to use string walking. Examples for string walking (described in the excellent <a href=\"http:\/\/www.amazon.com\/Cookbook-Cookbooks-OReilly-Anthony-Molinaro\/dp\/0596009763\">SQL Cookbook<\/a>) can be found <a href=\"http:\/\/code.openark.org\/blog\/mysql\/unwalking-a-string-with-group_concat\">here<\/a>, <a href=\"http:\/\/code.openark.org\/blog\/mysql\/checking-for-string-permutation\">here<\/a> and <a href=\"http:\/\/code.openark.org\/blog\/mysql\/rotating-sql-graphs-horizontally\">here<\/a>. We&#8217;ll be using a numbers table: a simple table which lists ascending integer numbers. For example, you can use the following:<\/p>\n<blockquote>\n<pre>DROP TABLE IF EXISTS `tinyint_asc`;\r\n\r\nCREATE TABLE `tinyint_asc` (\r\n `value` tinyint(3) unsigned NOT NULL default '0',\r\n PRIMARY KEY (value)\r\n) ;\r\n\r\nINSERT INTO `tinyint_asc` VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);<\/pre>\n<\/blockquote>\n<p>The trick is to apply the same technique as used above, not for a single row, but for several rows. Here&#8217;s how to present the top <strong>5<\/strong> countries:<\/p>\n<blockquote>\n<pre>SELECT\r\n  Continent,\r\n  SUBSTRING_INDEX(\r\n    SUBSTRING_INDEX(\r\n      GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),\r\n      ',', value),\r\n    ',', -1)\r\n    AS Name,\r\n  CAST(\r\n    SUBSTRING_INDEX(\r\n      SUBSTRING_INDEX(\r\n        GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),\r\n        ',', value),\r\n      ',', -1)\r\n    AS DECIMAL(20,2)\r\n    ) AS SurfaceArea,\r\n  CAST(\r\n    SUBSTRING_INDEX(\r\n      SUBSTRING_INDEX(\r\n        GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),\r\n        ',', value),\r\n      ',', -1)\r\n    AS UNSIGNED\r\n    ) AS Population\r\nFROM\r\n  Country, tinyint_asc\r\nWHERE\r\n  tinyint_asc.value &gt;= 1 AND tinyint_asc.value &lt;= 5\r\nGROUP BY\r\n  Continent, value\r\n;\r\n+---------------+----------------------------------------------+-------------+------------+\r\n| Continent\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | SurfaceArea | Population |\r\n+---------------+----------------------------------------------+-------------+------------+\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | China\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 9572900.00 | 1277558000 |\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | India\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 3287263.00 | 1013662000 |\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Kazakstan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2724900.00 |\u00a0\u00a0 16223000 |\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Saudi Arabia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2149690.00 |\u00a0\u00a0 21607000 |\r\n| Asia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Indonesia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1904569.00 |\u00a0 212107000 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Russian Federation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 17075400.00 |\u00a0 146934000 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Ukraine\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 603700.00 |\u00a0\u00a0 50456000 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | France\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 551500.00 |\u00a0\u00a0 59225700 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Spain\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 505992.00 |\u00a0\u00a0 39441700 |\r\n| Europe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Sweden\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 449964.00 |\u00a0\u00a0\u00a0 8861400 |\r\n| North America | Canada\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 9970610.00 |\u00a0\u00a0 31147000 |\r\n| North America | United States\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 9363520.00 |\u00a0 278357000 |\r\n| North America | Greenland\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2166090.00 |\u00a0\u00a0\u00a0\u00a0\u00a0 56000 |\r\n| North America | Mexico\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1958201.00 |\u00a0\u00a0 98881000 |\r\n| North America | Nicaragua\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 130000.00 |\u00a0\u00a0\u00a0 5074000 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Sudan\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2505813.00 |\u00a0\u00a0 29490000 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Algeria\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2381741.00 |\u00a0\u00a0 31471000 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Congo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2344858.00 |\u00a0\u00a0 51654000 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 The Democratic Republic of the\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1759540.00 |\u00a0\u00a0\u00a0 5605000 |\r\n| Africa\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Libyan Arab Jamahiriya\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1284000.00 |\u00a0\u00a0\u00a0 7651000 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Australia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 7741220.00 |\u00a0\u00a0 18886000 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Papua New Guinea\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 462840.00 |\u00a0\u00a0\u00a0 4807000 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | New Zealand\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 270534.00 |\u00a0\u00a0\u00a0 3862000 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Solomon Islands\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 28896.00 |\u00a0\u00a0\u00a0\u00a0 444000 |\r\n| Oceania\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | New Caledonia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 18575.00 |\u00a0\u00a0\u00a0\u00a0 214000 |\r\n| Antarctica\u00a0\u00a0\u00a0 | Antarctica\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 13120000.00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| Antarctica\u00a0\u00a0\u00a0 | French Southern territories\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 7780.00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| Antarctica\u00a0\u00a0\u00a0 | South Georgia and the South Sandwich Islands |\u00a0\u00a0\u00a0\u00a0 3903.00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| Antarctica\u00a0\u00a0\u00a0 | Heard Island and McDonald Islands\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 359.00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| Antarctica\u00a0\u00a0\u00a0 | Bouvet Island\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 59.00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n| South America | Brazil\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 8547403.00 |\u00a0 170115000 |\r\n| South America | Argentina\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2780400.00 |\u00a0\u00a0 37032000 |\r\n| South America | Peru\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1285216.00 |\u00a0\u00a0 25662000 |\r\n| South America | Colombia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1138914.00 |\u00a0\u00a0 42321000 |\r\n| South America | Bolivia\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1098581.00 |\u00a0\u00a0\u00a0 8329000 |\r\n+---------------+----------------------------------------------+-------------+------------+<\/pre>\n<\/blockquote>\n<h4>Limitations<\/h4>\n<p>You should have:<\/p>\n<ul>\n<li>Enough numbers in the numbers table (I&#8217;ve used 5 out of 255)<\/li>\n<li>Reasonable setting for <strong>group_concat_max_len<\/strong> (see <a title=\"Those oversized, undersized variables defaults\" href=\"http:\/\/code.openark.org\/blog\/mysql\/those-oversized-undersized-variables-defaults\">this post<\/a>). Actually it would be better to have a smaller value here, while you make sure it&#8217;s large enough; this way you do not waste memory for large groups.<\/li>\n<\/ul>\n<p>(*) This was two years ago! I&#8217;m getting old<\/p>\n<h4>Update: see also<\/h4>\n<p>Another hack at same problem: <a title=\"Link to SQL: selecting top N records per group, another solution\" href=\"http:\/\/code.openark.org\/blog\/mysql\/sql-selecting-top-n-records-per-group-another-solution\" rel=\"bookmark\">SQL: selecting top N records per group, another solution<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A while back I presented(*) an SQL trick to present with non-aggregated column on a GROUP BY query, without use of subquery or derived tables. Based on a similar concept, combined with string walking, I now present a query which selects top-n records for each group, ordered by some condition. It will require no subqueries. [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[21],"class_list":["post-3164","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-P2","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3164","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3164"}],"version-history":[{"count":36,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3164\/revisions"}],"predecessor-version":[{"id":3234,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3164\/revisions\/3234"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}