Comments on: MySQL/QueryScript use case: DELETE all but top N records per group https://shlomi-noach.github.io/blog/mysql/mysqlqueryscript-use-case-delete-all-but-top-n-records-per-group Blog by Shlomi Noach Fri, 09 Mar 2012 04:00:30 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Rick James https://shlomi-noach.github.io/blog/mysql/mysqlqueryscript-use-case-delete-all-but-top-n-records-per-group/comment-page-1#comment-75067 Fri, 09 Mar 2012 04:00:30 +0000 https://shlomi-noach.github.io/blog/?p=4588#comment-75067 Here’s how to do a similar thing with US cities and states. I’ll leave it as an exercise to turn it into a DELETE.

# The three most populous cities in each state:
CREATE TEMPORARY TABLE t (
    seq SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (state, seq)
)  ENGINE = MyISAM   -- required for the PRIMARY KEY trick
SELECT state, city, population
    FROM US
    ORDER BY state, population DESC;
SELECT state, seq, city, population
    FROM t
    WHERE seq <= 3
    ORDER BY state, seq;
+-------+-----+--------------+------------+
| state | seq | city         | population |
+-------+-----+--------------+------------+
| AK    |   1 | Anchorage    |     276263 |
| AK    |   2 | Juneau       |      31796 |
| AK    |   3 | Fairbanks    |      31351 |
| AL    |   1 | Birmingham   |     231621 |
| AL    |   2 | Montgomery   |     198325 |
| AL    |   3 | Mobile       |     190274 |
| AR    |   1 | Little Rock  |     184217 |
| AR    |   2 | Fort Smith   |      81985 |
| AR    |   3 | Fayetteville |      64864 |
| AZ    |   1 | Phoenix      |    1428509 |
| AZ    |   2 | Tucson       |     518907 |
...
]]>