mysql select N max values per group

In this post:

  • Get N numbers per group
  • Get N max/min numbers per group
  • Get N numbers per group additional constraints
  • Table code and data

You could read also:
SQL select max and return N values
MySQL how to find highest and lowest value

Let say that you want to get N values or N max values per group in MySQL. For example get 3 persons from this table ( at the end you have creation code and data):

id country person
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

Our goal is to get N persons per country(in table below we take only 1 person per country):

id person country
2 Anie Austria
4 Brian Australia
6 Tim UK
9 Tom USA
13 Jamy N. Korea
14 Kimi Finland

Get N numbers per group

In order to get N numbers per group you can use special syntax in mysql using self join and count. In this example we are getting at least 2 person from country or 1 (if the country has less than N - 2):

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country 		-- controlling grouping column
AND co.id < ci.id 						-- controlling min or max 
) < 2   										-- controlling number of return per group
;

You can control the number N by changing the value: < 2 ,if you want 4 person per country you should put < 4

result:

id person country
1 Sue Austria
2 Anie Austria
3 John Australia
4 Brian Australia
5 Jim UK
6 Tim UK
8 Mike USA
9 Tom USA
12 Rick N. Korea
13 Jamy N. Korea
14 Kimi Finland

Get N max/min numbers per group

The control of getting max or min records is done by this statement:

co.id < ci.id

so based on the above table for North Korea we will have:

  • co.id < ci.id - this is getting maximum
id person country
12 Rick N. Korea
13 Jamy N. Korea
  • co.id > ci.id - this is getting minimum
id person country
10 Joe N. Korea
11 Hue N. Korea

So getting the minimum per group is

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id > ci.id
) < 1
;

result:

id person country
1 Sue Austria
3 John Australia
5 Jim UK
7 David USA
10 Joe N. Korea
14 Kimi Finland

And getting the maximum per group would be:

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;

result:

id person country
2 Anie Austria
4 Brian Australia
6 Tim UK
9 Tom USA
13 Jamy N. Korea
14 Kimi Finland

Get N numbers per group additional constraints

If you want to use additional constraints, for example to get only males or females this should be applied on both selects - inner and outer - otherwise you will get different result

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id and gender = 1
) < 2
and gender = 1;

This ensures getting the only people with gender = 1. Otherwise the result final result will include the rest of possible values for gender.

Table code and data

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');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (2, 'Austria', 'Anie');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (3, 'Australia', 'John');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (4, 'Australia', 'Brian');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (5, 'UK', 'Jim');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (6, 'UK', 'Tim');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (7, 'USA', 'David');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (8, 'USA', 'Mike');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (9, 'USA', 'Tom');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (10, 'N. Korea', 'Joe');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (11, 'N. Korea', 'Hue');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (12, 'N. Korea', 'Rick');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (13, 'N. Korea', 'Jamy');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (14, 'Finland', 'Kimi');

Related Article