By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `person` (
`id` INT(11) NULL DEFAULT NULL,
`country` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
`person` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin'
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;
INSERT INTO `person` (`id`, `country`, `person`) VALUES
(1, 'Austria', 'Sue'),
(2, 'Austria', 'Anie'),
(3, 'Australia', 'John'),
(4, 'Australia', 'Brian'),
(5, 'UK', 'Jim'),
(6, 'UK', 'Tim'),
(7, 'USA', 'David'),
(8, 'USA', 'Mike'),
(9, 'USA', 'Tom'),
(10, 'N. Korea', 'Joe'),
(11, 'N. Korea', 'Hue'),
(12, 'N. Korea', 'Rick'),
(13, 'N. Korea', 'Jamy'),
(14, 'Finland', 'Kimi');
SELECT co.id, co.person, co.country,
(
SELECT COUNT(*)
FROM person ci
WHERE co.country = ci.country -- controlling grouping column
AND co.id < ci.id -- controlling min or max
) AS higher_ids
FROM person co
| id | person | country | higher_ids |
|---|---|---|---|
| 1 | Sue | Austria | 1 |
| 2 | Anie | Austria | 0 |
| 3 | John | Australia | 1 |
| 4 | Brian | Australia | 0 |
| 5 | Jim | UK | 1 |
| 6 | Tim | UK | 0 |
| 7 | David | USA | 2 |
| 8 | Mike | USA | 1 |
| 9 | Tom | USA | 0 |
| 10 | Joe | N. Korea | 3 |
| 11 | Hue | N. Korea | 2 |
| 12 | Rick | N. Korea | 1 |
| 13 | Jamy | N. Korea | 0 |
| 14 | Kimi | Finland | 0 |