Re: basic join question

[Rick James]

# 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 |
...